Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Anonymous
Not applicable

Sum and display outer-hierarchy values when slicer is on "select all"

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Nevermind! Solved my own problem. Yay!

Measure = 
IF (
    ISINSCOPE ( 'Table'[category] ),
    BLANK (),
    CALCULATE(
        SUMX(
VALUES('Table'[Member count]), CALCULATE(MAX('Table'[Member count])))) )

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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. 

Anonymous
Not applicable

Nevermind! Solved my own problem. Yay!

Measure = 
IF (
    ISINSCOPE ( 'Table'[category] ),
    BLANK (),
    CALCULATE(
        SUMX(
VALUES('Table'[Member count]), CALCULATE(MAX('Table'[Member count])))) )

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.