cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
EmilyNomura Member
Member

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

Accepted Solutions
EmilyNomura Member
Member

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

Nevermind! Solved my own problem. Yay!

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

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

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. 

EmilyNomura Member
Member

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

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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 26 members 1,016 guests
Please welcome our newest community members: