MATRIX - drill up with custom sub-totals

[mrothschild]

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.

Collapser	Formatted Balance Sh. . . .	2018	2019
6	Assets
6	Total Assets	$300,017	$1,020,575
6	Liabiliites and Equity
6
6	Total Liabilities
6	Equity
6	Total 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!

[Greg_Deckler]

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

[mrothschild]

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

[v-juanli-msft]

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.

[mrothschild]

Sorry, here's a Dropbox Link that should work

[v-juanli-msft]

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)?

Best Regards
Maggie