Forum Discussion

powerinM's avatar
powerinM
Icon for Helper II rankHelper 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 through the slicer. In the link below where the data model has two dimension tables (cost centre and account hierarchy). First I would like to achieve topn (e.g 3) analysis based on cost centre tbl dimension showing top 3 over budget and top 3 under budget. After achieving that, is it possible to include what are top accounts contributing the variances (from the account hierarchy dimension). Any assistance would be greatly appreciated. Thanks

 

https://www.dropbox.com/s/wl8mf77fawg4k2w/Topn.pbix?dl=0

 

Need Help

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity 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
      Icon for Helper II rankHelper 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.