Anyone trying to create a Profit and Loss styled Excel Pivot table would wonder how can you show values in rows in a Pivot Table.

Imagine this simple data

Show values in Rows in a Pivot Table

To display the values in the rows of the pivot table, follow the steps

Show values in Rows in a Pivot Table

  1. Now when you start creating a pivot table
  2. Drag Dates into Columns
  3. Add the first field – Sales into Values
  4. Then add the second field – Expenses into Values
  5. You’ll see that “Σ” Values field in columns area
  6. Just drag that in rows and you are done!

Show Values on Rows in Power BI

The same can be achieved in Power BI too

Show values in Rows in a Pivot Table

  1. Create a Matrix Visual (i.e. a Pivot Table)
  2. Drag Years / Months in Columns
  3. Then Drag two or more fields / measures in values
  4. Go to the formatting tab of the Matrix
  5. Under Values you’ll find the option to show the values on rows – turn it on

You’ll end up having a pivot like this

Show values in Rows in a Pivot Table

Nifty.. huh!


Want more Excel / Power BI Tricks?

  1. How to Create a Pivot Table in Power BI
  2. COUNTIF in Power BI using DAX
  3. Filter Data using Multiple Conditions using Power Query
  4. Nice and Fast – Filter Shortcuts in Excel
  5. Create Hyperlinked Index of Sheet Names in Excel


Topics that I write about...

Download Smart Ebooks on
Excel and Power BI