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,

 

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

    • DimitrisBech's avatar
      DimitrisBech
      Frequent Visitor
      7 years ago

      Hi Anonymous,

       

      and thank you for your answer.

      I do not understand your approach.

      Is the below what you are referring to?

      Variance Table

      Alternatively, can you/someone please offer some help, in my fileset?

       

      Best,

      DB

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        HI DimitrisBech,

         

        I mean you can create a variable to store filtered summary table and add new column to store measure result, then use sumx or other function to get result from variable table.


        In addition, I found you already create a transform data table, Maybe you can try to use allselected function to get filtered records, then you summary records with specific conditions.

         

        Measure =
CALCULATE (
    SUM ( Table[Arrangement Revenues] ),
    FILTER ( ALLSELECTED ( Table ), Table[Period] = "Actual" )
)
    - CALCULATE (
        SUM ( Table[Arrangement Revenues] ),
        FILTER ( ALLSELECTED ( Table ), Table[Period] = "Budget" )
    )

         

        Regards,

        Xiaoxin Sheng