Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more
Revisiting one of my previous questions (https://community.powerbi.com/t5/Desktop/Only-Display-Outer-Measure-in-Matrix-Row-Hierarchy/m-p/7241...), I have a matrix with multiple hierarchies.
Before, I was able to display the correct member count in the outer hierarchy row according to a users response:
Measure 2 =
IF (
ISINSCOPE ( 'Table'[category] ),
BLANK (),
DIVIDE (
CALCULATE ( SUM ( 'Table'[Member count] ) ),
DISTINCTCOUNT ( 'Table'[category] )
)
)Now I have added many drill down rows to Category1 and Category2. I've gotten around the issue and have the correct member counts shown in the right out-hierarchy by using
Measure =
IF (
ISINSCOPE ( 'Table'[category] ),
BLANK (),
CALCULATE(
MAX( 'Table'[Member count] ) )
)However, when I use my slicer to select both (of 2) filter categories, my member count does not sum -- it simply takes the max so it is really only displaying one of my slicer filter categories. I understand that this is due to the definition of my measure. But since each Category1 and Category2 of each year may have a different number of drill-down rows, I am not able to implement the first measure definition because it utilizes DISTINCTCOUNT. Any ideas on how I would be able to display the sum on the outer-hierarchy row? I have the same issue with two of my other columns that sum on the outer-hierarchy level rather than average.
Solved! Go to Solution.
Nevermind! Solved my own problem. Yay!
Measure =
IF (
ISINSCOPE ( 'Table'[category] ),
BLANK (),
CALCULATE(
SUMX(
VALUES('Table'[Member count]), CALCULATE(MAX('Table'[Member count]))))
)
I was able to get a sum of sorts with
Measure =
IF (
ISINSCOPE ( 'Table'[category] ),
BLANK (),
CALCULATE(
SUMX(
VALUES('Table'[Member count]), MAX('Table'[Member count])))
)but, of course, when clicking "select all" on the slicer, the sum just gives me double the count for the slicer option with the highest member count.
Nevermind! Solved my own problem. Yay!
Measure =
IF (
ISINSCOPE ( 'Table'[category] ),
BLANK (),
CALCULATE(
SUMX(
VALUES('Table'[Member count]), CALCULATE(MAX('Table'[Member count]))))
)
The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!
| User | Count |
|---|---|
| 64 | |
| 45 | |
| 41 | |
| 36 | |
| 23 |
| User | Count |
|---|---|
| 192 | |
| 125 | |
| 106 | |
| 78 | |
| 53 |