Forum Discussion

powerinM's avatar
powerinM
Helper II
6 years ago

Top Budget Variance analysis

Hi: I am trying to create a dynamic topn dax measure which will show top variances (actual against budget) for both under and over spend. The selection of number of top variances will be controlled t...
Need Help
v-frfei-msft's avatar
v-frfei-msft
Community Support
6 years ago

Hi powerinM ,

 

We can create a What if parameter in your scenario.

Then we can update your formula as below.

TopVar Analysis2 =
IF (
    ISINSCOPE ( 'Cost Centre Ranking'[Ranking group] ),
    VAR NumOfCC = Parameter[Parameter Value]
    VAR RankingGroup =
        SELECTEDVALUE ( 'Cost Centre Ranking'[Ranking group] )
    VAR TopCostCentre =
        TOPN ( NumOfCC, ALLSELECTED ( 'Cost Centre Ranking' ), [YTD Variance] )
    RETURN
        SWITCH (
            RankingGroup,
            "Over/Under Budget Cost Centre", CALCULATE ( [YTD Variance], KEEPFILTERS ( TopCostCentre ) ),
            "Other", IF (
                NOT ISINSCOPE ( 'Cost Centre Ranking'[Ranking CostCentre] ),
                VAR TopAmount =
                    CALCULATE ( [YTD Variance], TopCostCentre )
                VAR AllAmount =
                    CALCULATE ( [YTD Variance], ALLSELECTED ( 'Cost Centre Ranking' ) )
                VAR OtherAmt = AllAmount - TopAmount
                RETURN
                    OtherAmt
            )
        ),
    [YTD Variance]
)

 

 

powerinM's avatar
powerinM
Helper II
6 years ago

Thanks for your reply. Sorry I dont think the idea of using what-if parameter sits well with topn analysis. I want to acheive the topn analysis through dax measure which is goiing to show not only top over spend cost centres against budget but also top under spend against budget depends on the number selection. After that bringing in account hierarchy dimension into the analysis.