Forum Discussion
How to do aggregation in column values instead of rows
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
hi dedelman_clng,
Thanks for your reply.
This is the sample data structure
| City | yearmon | # of Bills | # of Customers | Revenue | # of Repeat Customers | Revenue from Repeat Customers |
| A | 201601 | 2147 | 1735 | 525074 | 1054 | 400297 |
| A | 201602 | 2678 | 2114 | 784000 | 1326 | 564902 |
| A | 201603 | 2520 | 2009 | 503934 | 1169 | 210811 |
| A | 201604 | 2000 | 1844 | 680402 | 950 | 423141 |
| A | 201605 | 2000 | 1968 | 7829691 | 1142 | 299003 |
| B | 201601 | 2592 | 2079 | 1014188 | 1135 | 544405 |
| B | 201602 | 2605 | 2060 | 1068080 | 1020 | 779486 |
| B | 201603 | 2352 | 1898 | 927795 | 881 | 628698 |
I have City in filter and I want to show month on month sum of bills, customers, revenue etc calculated. I want 201601, 201602 to come in separate columns like in my earlier post.
Looking forward to your reply.
Thanks
Rohit
Super UserHi 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