Forum Discussion

Frequent Visitor

9 years ago

Solved

How to do aggregation in column values instead of rows

Hi,

I am a bit novice in power BI and just starting up.

What I basically want to know is if summation/count etc can be put in first column rather than adding them in rows?

This is how it looks in excel.

Can we create such looking matrix or table in power BI?

Thanks

Rohit

MFelix
9 years ago
Hi rohitthb,

Looking at your data I believe that the best way to do this is to unpivot the information you have and then do the matrix, this because if you add the columns you want to calculate to the matrix visual you will get the calculations but the name will not be there, again this is based on the information you made available.

I the Power Query view do as follow:
- Select all the columns to be calculated
- Unpivot columns

This will give you a new format with:
- City
- Value
- Yearmon
- Attribute - As the name of the columns # bills, # Customers,...

Then just add YearMon as columns, Value as value and attibute as Columns. You can then filter the attibute in the matrix visual if you don't want everything to show in the table or add a slicer. check the print screnns below.

Regards,

MFelix

7 Replies

MFelix
Super User
9 years ago
Hi rohitthb,

If you select the Matrix visuals you can add the rows/columns as you need.

Regrads,

MFelix
- rohitthb
  Frequent Visitor
  9 years ago
  Hi MFelix,
  
  Thanks for your reply. I am not able to figure out the option which enables me to get aggregation in the column.
  In excel this options come by dragging summation from column into rows in pivot.
  
  I am clueless how to do this in power BI. If you could guide me it will be helpful.
  
  Thanks
  Rohit
  - dedelman_clng
    Community Champion
    9 years ago
    Hi rohitthb - do you want the final table to look like the picture in your original post? If so, where is the value 201601 coming from? Is 201601 the name of a column in your table, or is that the value in a column ? Some more information on where you are starting and exactly what you are trying to achieve would be helpful to troubleshoot.