Participation inside a Hierarchy

[Pedro_R]

I have a dataset with a hierarchy and value, something like this:

Level 1	Level 2	Level 3	Value
a	category 1	class 1	300
a	category 1	class 2	200
a	category 2	class 1	500

I need to know how I could see the % respected each level inside a Descomposition Tree or Matrix to display it in the same column.

For example if I select only level 1 and level 3 I have to display 80% for class 1, but if I select the levels 1 and 2 I have to see 50% in category 1.

How I could get it?

[v-deddai1-msft · Accepted solution]

Hi,

You can use ISINSCOPE function to calculate it:

Measure =

VAR A =

    CALCULATE ( SUM ( 'Table'[Value] ), ALL ( 'Table' ) )

RETURN

    IF (

        ISINSCOPE ( 'Table'[Level 2] ),

        IF (

            ISINSCOPE ( 'Table'[Level 3] ),

            SUM ( 'Table'[Value] ) / A,

            CALCULATE (

                SUM ( 'Table'[Value] ),

                FILTER ( 'Table', 'Table'[Level 2] IN DISTINCT ( 'Table'[Level 2] ) )

            ) / A

        ),

        CALCULATE (

            SUM ( 'Table'[Value] ),

            FILTER ( 'Table', 'Table'[Level 1] IN DISTINCT ( 'Table'[Level 1] ) )

        ) / A

    )

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@Pedro_R , refer if this can help

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