Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm facing a unique problem with sub-totals on Power BI Matrix Report. I've created a subtotal on one of the grouping column called "Subinventory". Also, on the same column, I've defined a dropdown filter. Now, the sub-total works fine when I select a particular subinventory from the filter. However, the matrix report is not displaying the sub-totals at all when the Subinventory filter is set to "All".
I've been trying to resolve this issue for past one day and have looked all around the web and nothing seems to point me in the right direction. Can anyone please guide/help me in finding what's the issue here and the solution?
Solved! Go to Solution.
To achieve your desired output as it looks on the picture you've posted follow these steps:
1) your Matrix should have Warehouse, Location and Items in the Rows and Qty, Cost and Value in the Values area
(this Cost column may have to be replaced by a Measure like the one below)
2) so select your Matrix and then click the Format button (Paint Brush)
3) open the Row Headers options => toggle Stepped Layout => Off => close the Row Headers options
3) open the Subtotals options => in Row subtotal label => replace the Total text => type Sub-Total
4) while still in the Subtotals options => scroll down to the bottom option => Per row level and turn it On
5) you will now see 3 addtional options to control the labels and subtotals for Warehouse, Location and Item
6) turn Off both Warehouse and Location (you only want Subtotals at the Item level so leave those on)
That should do it! 🙂
If you get a subtotal for the Cost which you don't want to show you can write a simple Measure to use instead.
Cost Measure = IF ( HASONEVALUE ( 'Table'[Item] ), MIN ( 'Table'[Cost] ), BLANK() )
Hope this helps!
Hi @onbi,
The reply of @Sean made me realize I didn't understood the real issue. I have created a PBIX file that contains your desired matrix, you can download it here: https://1drv.ms/u/s!Ancq8HFZYL_aiIw_YXDrEgo8Q5u5lg?e=SHj3pp
The matrix in the PBIX file looks like this:
Let me know if you need any more help 🙂 And thanks @Sean !
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Could you please provide some screenshots/mock up data/desired output? Based on this description alone it is very hard to help you I think.
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Sorry, I should've provided the current report vs. desired output via mock up data to help explain the problem. Below is the current report output vs. what I want out of sub-total feature on matrix report.
Current Output
Warehouse | Location | Item | Qty | Cost | Value |
Toronto | ABC | 123 | 1 | 29 | 29 |
345 | 2 | 10 | 20 | ||
EFG | 923 | 1 | 89 | 89 | |
846 | 3 | 20 | 60 | ||
345 | 10 | 17 | 170 | ||
RIT | 920 | 1 | 12 | 12 | |
809 | 5 | 70 | 350 | ||
Sub-Total | 23 | 247 | 730 |
Desired Output
Warehouse | Location | Item | Qty | Cost | Value |
Toronto | ABC | 123 | 1 | 29 | 29 |
345 | 2 | 10 | 20 | ||
Sub-Total | 3 | 49 | |||
EFG | 923 | 1 | 89 | 89 | |
846 | 3 | 20 | 60 | ||
345 | 10 | 17 | 170 | ||
Sub-Total | 14 | 319 | |||
RIT | 920 | 1 | 12 | 12 | |
809 | 5 | 70 | 350 | ||
Sub-Total | 6 | 362 |
To achieve your desired output as it looks on the picture you've posted follow these steps:
1) your Matrix should have Warehouse, Location and Items in the Rows and Qty, Cost and Value in the Values area
(this Cost column may have to be replaced by a Measure like the one below)
2) so select your Matrix and then click the Format button (Paint Brush)
3) open the Row Headers options => toggle Stepped Layout => Off => close the Row Headers options
3) open the Subtotals options => in Row subtotal label => replace the Total text => type Sub-Total
4) while still in the Subtotals options => scroll down to the bottom option => Per row level and turn it On
5) you will now see 3 addtional options to control the labels and subtotals for Warehouse, Location and Item
6) turn Off both Warehouse and Location (you only want Subtotals at the Item level so leave those on)
That should do it! 🙂
If you get a subtotal for the Cost which you don't want to show you can write a simple Measure to use instead.
Cost Measure = IF ( HASONEVALUE ( 'Table'[Item] ), MIN ( 'Table'[Cost] ), BLANK() )
Hope this helps!
Thank you @Sean for the detailed steps. Yes, I later realized that in order to group by location and have sub-total, I need to have sub-total at Item level. I think. its just the way the grouping is performed by Power BI on Matrix report.
Based on the DAX expression for not displaying the sub-total for the cost, I believe that, there is no switch to turn of the sub-total for a particular value in the report.
Proud to be a Super User!
Thank you djerro123. Yes, I've it the per row level switched on and the Location Sub-total is set to On as well, while for rest of the datapoints it's set to off. But, with that function, I'm seeing the current output and not the output, which I need.
However, when I switch on the Per Row Level for Item, it's then that I get the Sub-Total for each location.
I guess, it's just the way the data hierarchy and grouping done by Power BI Tool while generating a matrix report. 🙂 Any thoughts?
Proud to be a Super User!
Thank you djerro123 for your help. This is the first time I'm developing a Matrix Report in Power BI with Sub-Total.
I dont see how I can share my pbix file with you as I dont see an option to attach the file.
Proud to be a Super User!
Hi @onbi,
The reply of @Sean made me realize I didn't understood the real issue. I have created a PBIX file that contains your desired matrix, you can download it here: https://1drv.ms/u/s!Ancq8HFZYL_aiIw_YXDrEgo8Q5u5lg?e=SHj3pp
The matrix in the PBIX file looks like this:
Let me know if you need any more help 🙂 And thanks @Sean !
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Hi,
I have the same issue. I want result as above but I can't check your link. Also I have power BI June 2023 and this subtotals don't work like I want them to. Could you please help me?
Hi I also want to subtotal for item can anyone help me in this
Thank you @JarroVGIT for the report. I guess it was just my lack of understanding as to how the grouping works in Power BI Matrix report. Thanks for clarifying it. 🙂 This helps!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |