Subtotals on Matrix Report

[onbi]

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?

[Sean · Accepted solution]

@onbi 

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!