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

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.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors