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.
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])))) )
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |