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 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!
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
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?
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.
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
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |