Calculate the Sum of a column, depending on hierarchy

[Anonymous]

Hi experts,

I' stucked in this problem: Having this Matrix. Having a hierearchy Code->Type->Status->Course->Product, I would like to calculate the Sum of Population , divided by the number of the registers, depending on the level of hierarchy: 

In this case, the measure "Pop x grade" is doing well : 

Pop x Grade = SUM('Fact'[Population]) / 
COUNTROWS('Fact')

 

However, If I go up two levels of the hierarchy, I would like to sum "Pop x Grade"

The result of win, should be the sum of (81+78+77+78+40+76+77+76+79+79)=741

How can I create such a measure? I'm trying to use "X" functions but I'm stucked on it.

Any idea?

You can find a sample PBIX  here

Thanks for your time

[CNENFRNL · Accepted solution]

Hi, @Anonymous , pls try measure

Segmentation = 
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Fact'[Product] ) || ISINSCOPE ( 'Fact'[Course] ), [Pop x Grade],
    ISINSCOPE ( 'Fact'[Probability] ),
        SUMX (
            VALUES ( 'Fact'[Course] ),
            [Pop x Grade]
        ),
    ISINSCOPE ( 'Fact'[Type] ), "Type", // change to any calculation at your own will
    "Code" // change to any calculation at your own will
)

As to "Type" or "Code" level, you may use any other calculation at your own will.

[hailey185]

Hi @CNENFRNL !
How can I change the line  at "Type" and "Code" level if I want to have the results at "Type" equal sum of "Probability" defined in the previous ISINSCOPE clause? With the same logic, I would like to see results at "Code" level equal sum of "Type". I have tried this but it did not show correct total amount at "Type" and "Code" level.

Segmentation = 
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Fact'[Product] ) || ISINSCOPE ( 'Fact'[Course] ), [Pop x Grade],
    ISINSCOPE ( 'Fact'[Probability] ),
        SUMX (
            VALUES ( 'Fact'[Course] ),
            [Pop x Grade]
        ),
    ISINSCOPE ( 'Fact'[Type] ),
        SUMX (
            VALUES ( 'Fact'[Probability] ),
            [Pop x Grade]
        ),
    ISINSCOPE ( 'Fact'[Code] ),
        SUMX (
            VALUES ( 'Fact'[Type] ),
            [Pop x Grade]
        )
)

Thank you in advance!! 

[amitchandak]

@Anonymous , Depending on the hierarchy if you want to change totals you can us isinscope

example: Different calculation at a different level

Measure =
SWITCH (
TRUE,
ISINSCOPE ( 'Table'[State] ), [Costing],
ISINSCOPE ( 'Table'[City] ), [Costing1],
blank()
)

refer for more details  : https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Ajinkya369]

Hey @Anonymous ,

Please follow this blog post:

https://www.daxpatterns.com/parent-child-hierarchies/

If your problem is solved then please accept this reply as a solution.

Thank you

Ajinkya

Master Data Analysis - 

[Greg_Deckler]

@Ajinkya369 - I created MM3TR&R for this purpose, having different aggregations/calculations at different levels of a hiearchy.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...