cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Subtotals on Matrix Report

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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Subtotals on Matrix Report

@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! 

View solution in original post

Highlighted
Super User I
Super User I

Re: Subtotals on Matrix Report

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:

image.png

 

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! 🙂

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

11 REPLIES 11
Highlighted
Super User I
Super User I

Re: Subtotals on Matrix Report

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Subtotals on Matrix Report

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

WarehouseLocationItemQtyCostValue
TorontoABC12312929
  34521020
 EFG92318989
  84632060
  3451017170
 RIT92011212
  809570350
 Sub-Total 23247730

 

Desired Output

WarehouseLocationItemQtyCostValue
TorontoABC12312929
  34521020
 Sub-Total 3 49
 EFG92318989
  84632060
  3451017170
 Sub-Total 14 319
 RIT92011212
  809570350
 Sub-Total 6 362
Highlighted
Super User I
Super User I

Re: Subtotals on Matrix Report

Hi,

Please have a look at this article: https://www.ehansalytics.com/blog/2019/4/21/the-matrix-visual-and-ways-to-show-subtotals
In the format section of your matrix, you can alter subtotal behavior. If you want some subtotals, but not at every level, change the setting in the Subtotals section, Per row level settings. If Per row level is set to off, you’ll have subtotals on or off for every row you have data in. Turn it off for every rowlevel and turn it on for Location level 🙂




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Subtotals on Matrix Report

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?

Highlighted
Super User I
Super User I

Re: Subtotals on Matrix Report

Ok then please share your pbix because this is standard functionality and if my solution doesn't work the either we found a big or something else is influencing the result. You can share the link here in in PM if you don't want to share publicly.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Subtotals on Matrix Report

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.

Highlighted
Community Champion
Community Champion

Re: Subtotals on Matrix Report

@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! 

View solution in original post

Highlighted
Super User I
Super User I

Re: Subtotals on Matrix Report

Hi, you can use onedrive or Google drive for example to share via link 🙂




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User I
Super User I

Re: Subtotals on Matrix Report

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:

image.png

 

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! 🙂

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors