Calculate moving totals

[Anonymous]

I need to calculate moving total based on the selection of the drill down columns. For eg, in first step it to calculate Apparel by Total GL (values from column 1  ie 25229318/88093701 and ratio column contains the corresponding percentage)

In next step, i want to calculate Column 1 value divided by Pricing Driver 1 total , which comes after drilling down Apparel (ie. column1/total of Apparelor Pricing Driver 1, 3015266/25229318) instead of total of entire GL column

So, if i expand Base Pricing from pricing driver 1 column i should take that total in denominator.

That is how i need to change my total in denominator based on selection in column drill downs.

Kindly help me in finding the moving total based on selection.

@amitchandak  Please help me out on the same as i got correct result last time when u shared your experience.

[MFelix · Accepted solution]

Hi @Anonymous ,

Try the following measure:

Ratio =
IF (
    ISINSCOPE ( 'Table'[Sub CAt] ),
    DIVIDE (
        SUM ( 'Table'[Value] ),
        CALCULATE ( SUM ( 'Table'[Value] ), ALLSELECTED ( 'Table'[Sub CAt] ) )
    ),
    DIVIDE (
        SUM ( 'Table'[Value] ),
        CALCULATE ( SUM ( 'Table'[Value] ), ALLSELECTED ( 'Table'[CAt] ) )
    )
)

Has you can see below the results for the Main categories re in line with the second column that is the calculation of the % of Column:

[v-henryk-mstf]

Hi @Anonymous ,

Whether the advice given by @MFelix  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous · Accepted solution]

@MFelix , Thanks for the reply, i have 3 step drill down, how do we calculate for 3rd step drill down column

[MFelix]

Try the following setup:

Ratio =
SWITHC(TRUE(),

    ISINSCOPE ( 'Table'[Level3] ),
    DIVIDE (
        SUM ( 'Table'[Value] ),
        CALCULATE ( SUM ( 'Table'[Value] ), ALLSELECTED ( 'Table'[Level 3] ) )
    ),

    ISINSCOPE ( 'Table'[Level2] ),
    DIVIDE (
        SUM ( 'Table'[Value] ),
        CALCULATE ( SUM ( 'Table'[Value] ), ALLSELECTED ( 'Table'[Level 2] ) )
    ),
       ISINSCOPE ( 'Table'[Level1] ),
    DIVIDE (
        SUM ( 'Table'[Value] ),
        CALCULATE ( SUM ( 'Table'[Value] ), ALLSELECTED ( 'Table'[Level 1] ) )
    )
)

Be very aware of the order.

[MFelix · Accepted solution]

Hi @Anonymous ,

Try the following measure:

Ratio =
IF (
    ISINSCOPE ( 'Table'[Sub CAt] ),
    DIVIDE (
        SUM ( 'Table'[Value] ),
        CALCULATE ( SUM ( 'Table'[Value] ), ALLSELECTED ( 'Table'[Sub CAt] ) )
    ),
    DIVIDE (
        SUM ( 'Table'[Value] ),
        CALCULATE ( SUM ( 'Table'[Value] ), ALLSELECTED ( 'Table'[CAt] ) )
    )
)

Has you can see below the results for the Main categories re in line with the second column that is the calculation of the % of Column: