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 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.