Forum Discussion

DimitrisBech's avatar
DimitrisBech
Frequent Visitor
7 years ago

KPI Variance Visualization with any filtered combination

Hi to all fellow Datanauts,   It will be very much appreciated if you can help on how to tackle a variance calculation between KPI's. Table and data are formated as per below logic.   Now the ac...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

HI DimitrisBech,

 

Can you please provide more detailed information about this? If is hard to test  without any formula and detail information.

How to Get Your Question Answered Quickly

 

>> It will add up the effect, whereas the correct workaround would be to calculate the weighted average of the Arrangement Revenues to Roomnights Sold ratio for instance.

I think you need to add additional column to your visual as category to prevent the graph auto summarize.

 

Regards,

Xiaoxin Sheng

DimitrisBech's avatar
DimitrisBech
Frequent Visitor
7 years ago

Dear Xiaoxin, I think my presentation is detailed. The problem arises if I choose multiple Business Units for the ADR calculation. The correct calculation would be to add the fraction (Arrangement Revenue/# of Roomnights Sold) of Actuals and subtract the relevant Budget or LY fraction (Arrangement Revenue/# of Roomnights Sold). Given the table layout (snapshot in first post), what is correct DAX formula that would calculate this? I tried using the Variance per Category, Quick Measure with no luck.

 

Any ideas would be very appreciated.

 

In my opinion the only workaround would be to unpivot all metrics, grouped with value columns already in the table.

Thanks,

DB

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI DimitrisBech,

     

    Yes, I know your description is clearly enough.


    I mean we need some sample data and measure formula for test, if it is possible to achieve your requirement.(I can't get your formulas from snapshot.)

     

    Regards,

    Xiaoxin Sheng

    • DimitrisBech's avatar
      DimitrisBech
      Frequent Visitor
      7 years ago

      Dear Anonymous,

       

      Thank you for your reply.

      Below the actual PowerBi file.

      Test File

      I have amended the tables for making our calculations much more easier.

       

      Table that I am refering to, is the one named "ConsoData".

      The problem is how to calculate the difference of ADR (which is arrangement revenues divided by  # of Roomnights Sold) between Actuals and Budget.

      However the tricky part, is that the calculation would have to work dynamic and not add this effect when I am choosing multiple Business Units or Months. 

       

      Thank you and hoping that the above (and attached) are clear.

      DB

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        HI DimitrisBech,

         

        I'd like to suggest you add a variable table to measure with summarize function to summarize filter records from 'allselected' table and add ADR measure calculation to summary table.

         

        Then you can simply calculate on this temporary summary table to help calculate out which you wanted.

         

        Regards,

        Xiaoxin Sheng