KPI Variance Visualization with any filtered combination

[DimitrisBech]

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 actual calculation that we need to apply is the visualization of the variance for ADR (or ratio Arrangement Revenues to Roomnights Sold) between:

1. Actual vs Budget
2. Actual vs LY
3. Actual vs RF_SEP

With the combination of the below filters / parameters

1. Different or Grouped Business Units
2. Different or Grouped Resorts
3. Different or Grouped Resorts
4. Or all together or at any combination of the pointed “dimensions”

 

If I wanted to visualize in a “locked” view per Business Unit, per distinct Rep Date and get the correct results, I would make a bar chart and get a measure that would calculate columns i.e. Actual vs Budget. That would work at the lowest level of visualization i.e. Axis would contain Rep Date and Metric, whereas Values would contain Actual, Budget, and the measure of the Variance between these two. Filters would be Resort and Business Unit.

Below is the actual visualization:

 

However, if I wanted to Group per multiple Rep_Dates and or Different combination of Business Units, ADR calculation will not produce the result. 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.

Can you please provide with some ideas on how to make the most complete Variance chart, having in mind the above problem?

Thank you so much.

[v-shex-msft]

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]

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

[v-shex-msft]

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]

Dear @v-shex-msft,

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

[v-shex-msft]

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]

Hi @v-shex-msft,

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

[v-shex-msft]

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