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

MATRIX - drill up with custom sub-totals

PBIX file

 

I have a Matrix using a specific set of Measures to return intended results when the rows are fully drilled down.  What I'd like is to be able to drill up and return sub-totals (which are already displayed in full drill down), so that the bottom panel of the below screenshot will show the Matrix with the data displayed as the table below.  

 

I suspect that ISINSCOPE() or HASONEVALUE() 

 

may be the keys to this, but not familiar with their programming architecture.

 

CollapserFormatted Balance Sh. . . .20182019
6Assets  
6Total Assets$300,017$1,020,575
6Liabiliites and Equity  
6   
6Total Liabilities  
6Equity  
6Total Liabilities and Equity$109,368$586,428

 

Ideally, I'd be able to drill up further, and show all rows where collapser is 6 or 7, etc. but if I can just get it to 6, then that would be fine for now.

 

Thanks!

 

collapser.PNG

5 REPLIES 5
Highlighted
Super User IV
Super User IV

Re: MATRIX - drill up with custom sub-totals

I believe you want this: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

 

But here is my standard answer to measure total problems:

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Resolver I
Resolver I

Re: MATRIX - drill up with custom sub-totals

@Greg_Deckler 

 

Thanks for this, I'm getting oh so close.  I had to make some minor tweaks to the Triple Threat Measure, and when drilled up, I'm getting sub-totals to work as intended, EXCEPT, for rows that I don't want displayed when drilled up.  I've changed the [collapser] column so the data is 1,2,3,4,999 and when drilled up, I only want to show 1,2,3,4.  If I remove the 999 and leave it blank and then turn off blanks, then it won't show when drilled down.

 

Any suggestions?

Highlighted
Community Support
Community Support

Re: MATRIX - drill up with custom sub-totals

Hi @mrothschild 

We have no access to your pbix file.

If your pbix file contains no privacy data, you could upload it to OneDrive and share the link with me.

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Resolver I
Resolver I

Re: MATRIX - drill up with custom sub-totals

Sorry, here's a Dropbox Link that should work

 

 

Highlighted
Community Support
Community Support

Re: MATRIX - drill up with custom sub-totals

Hi @mrothschild 

Do you mean when you drill down, the matrix shows correct values, but when drill up, the matrix doesn't show correct "subtotal" value for the row (1,2,3,4)?

Capture7.JPGCapture8.JPG

Best Regards
Maggie

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

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

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors