Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi, I have some data that I would like to put in a matrix. The matrix has two hierarchy levels:
1) Scenario Parent
2) Scenario
That causes it to look like this when expanded:
The slicers filter the Scenario Parent (outer-most row) and also the Sales Delta (the % value that some sub-rows have. Additionally, that "All Consulting" row should always be there. I have set-up the slicers such that it remains there. Here's an example filter:
This is fine. The issue is when I drill up. I get this:
What I don't want is the values in the "Tech & Management Consulting" Row. The Row should be there, but it should be empty. This is because I don't want the viewers to get the impression that "Tech & Management Consulting" Scenario Parent can have a value. It's only values are those of its sub-rows (+50% Sales and -50% Sales). I would like the Values in the "All Consulting" Scenario Parent to remain.
Is it possible to only SUM() values at the Scenario Parent level if the value is equal to "All Consulting"? And otherwise only do the summing at the Scenario level. And display blank values when the non-"All Consulting" rows are drilled up.
I tried some measures but I ended up getting two undesired behaviors:
1) When drilled-up, the non-"All Consulting" rows would completely disappear. I don't want this. I want them to still be there, just as blanks, or maybe as "-" values.
2) When drilled-up, all the non-"All Consulting" rows are appearing, even when not selected by the slicer. This happened when I tried to set values as "-" if not in the Scenario Scope. Also don't want this.
This is what the data looks like:
And:
The values in 6_Slicers_Test_Data are used in the slicers for the matrix. The tables are connected like this:
The data can be found here: https://docs.google.com/spreadsheets/d/1gLlManq2v1K61Wej8NCNV3m2rxIKF7mj/edit?usp=sharing&ouid=10049...
Does anyone know if there's a measure that can return blank values (but not hide the row) if it is selected by the slicer and in hierarchy "Scenario Parent"? And it should SUM() for the "Scenario" hierarchy. If the row is of "All Consulting" (same value in Parent and non-Parent Scenario hierarchy) then we sum in both levels.
Or, even more generally, if there are more than two sub-rows in a row, then I want the measure to return BLANK() for each column of that row. Otherwise, it can return the value.
I'd appreciate any insight on this. Thank you!
Solved! Go to Solution.
Hi @fourmj ,
Please try the measure. Then show items with no data in matrix.
Measure =
IF (
MAX ( '5_test_Data'[Scenario Parent] ) = "All Consulting",
SUM ( '5_test_Data'[Value] ),
IF ( ISINSCOPE ( '5_test_Data'[Scenario] ), SUM ( '5_test_Data'[Value] ) )
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @fourmj ,
Please try the measure. Then show items with no data in matrix.
Measure =
IF (
MAX ( '5_test_Data'[Scenario Parent] ) = "All Consulting",
SUM ( '5_test_Data'[Value] ),
IF ( ISINSCOPE ( '5_test_Data'[Scenario] ), SUM ( '5_test_Data'[Value] ) )
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thaanks for sharig this dax, what can we do in case we want 5_test_Data'[Scenario]
level sub total ?
Thanks in advance
User | Count |
---|---|
132 | |
74 | |
70 | |
58 | |
54 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |