Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @rohitthb,
If you select the Matrix visuals you can add the rows/columns as you need.
Regrads,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix,
First of all sorry for late response. I was out sick.
This is exactly what I was looking for and was not able to figure out. Super thanks for your answer.
Thanks
Rohit
User | Count |
---|---|
87 | |
84 | |
67 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |