Plan/Actual/Variance for absolute AND relative metrics in DAX (Excel, not PBI)

Question

I'm trying to find an unified approach to show Plan/Actual/Variance for both absolute and relative metrics. There are several datasets, which are generally structured similarly. They have

• Dates (supposed to group/expand/collapse)
• "pf" (plan/fact) - a plan/actual data marker
• Dimensions - platforms, regions, channels (different for each dataset, supposed to be grouped in different combinations and also expanded/collapsed)
• Absolute metrics may include expenses, sale amounts, some other values like weight shipped in logistics or clicks in ecom.
• Relative metrics like rates, avg prices, ratios etc. They are all defined as measures. I expect to see plan, actual and variance for both absolute and relative metrics in the same place, grouped by different criteria, like see expense and ROI for each channel, the whole platform, and all platforms aggregated. or average weight for different warehouses and different target regions.

Comparing absolute values only would be quite easy:

• unpivot metrics on preparation step, getting two columns: metric / value
• use "metric" as a dimension
• make three measures:

plan:=CALCULATE(sum(tbl[value]), pf="plan")
act (//same)
var:=DIVIDE(act,plan)-1

But having relative measures makes it harder

1. First I tried approach like this: an aux table called aspects with one column containing four values: plan, act, dif, var. It is brought into the main table without creating a relationship. Measures look something like this:

expense := SWITCH(
  IF(HASONEVALUE(aspects[aspect]), VALUES(aspects[aspect]), BLANK()), 
  "plan", CALCULATE(SUM(combined[expense]), combined[pf] = "plan"), 
  "act", CALCULATE(SUM(combined[expense]), combined[pf] = "fact"), 
  "dif", CALCULATE(SUM(combined[expense]), combined[pf] = "plan") - CALCULATE(SUM(combined[expense]), combined[pf] = "fact"), 
  "var", DIVIDE(CALCULATE(SUM(combined[expense]), combined[pf] = "fact"), CALCULATE(SUM(combined[expense]), combined[pf] = "plan")) - 1
)

2. Then I tried approach like this: an aux table called dimensions_list with a column like (let's consider an ecom as an example): expense | sales | clicks | orders | avg_order | roi | cr | ctr | cpc. I build measures in two stages: first, each individual metric. Absolute measures:

_orders := SUM(combined[orders])

Relative measures:

_avg_order := DIVIDE([_sales], [_orders])

Then aggregation:

plan := 
VAR _dimension = IF(HASONEVALUE(dimensions_list[dimension]), VALUES(dimensions_list[dimension]), BLANK())
RETURN SWITCH(
  _dimension, 
  "sales", CALCULATE([_sales], combined[pf] = "plan"),
  "expense", CALCULATE([_expense], combined[pf] = "plan"),
  "orders", CALCULATE([_orders], combined[pf] = "plan"),
  "clicks", CALCULATE([_clicks], combined[pf] = "plan"),
  "impressions", CALCULATE([_impressions], combined[pf] = "plan"),
  "avg_order", CALCULATE([_avg_order], combined[pf] = "plan"),
  "cpc", CALCULATE([_cpc], combined[pf] = "plan"),
  "cpm", CALCULATE([_cpm], combined[pf] = "plan"),
  "cr", CALCULATE([_cr], combined[pf] = "plan"),
  "ctr", CALCULATE([_ctr], combined[pf] = "plan"),
  "roi", CALCULATE([_roi], combined[pf] = "plan"),
  "expense_ratio", CALCULATE([_expense_ratio], combined[pf] = "plan")
)

act measure: (similar to plan) Var measure:

Var:= IF(
  ISBLANK([plan]) && ISBLANK([fact]), BLANK(),
  DIVIDE([fact], [plan]) - 1
)

In this case, we use the Excel's "Values" block as an additional dimension.

AND NOW THE QUESTION: In both approaches, the metrics "bar" (inserted from an aux table) does not appear in total (which is one of the goals of the entire task). It's doesn't appear in the grand total, neither in subtotals for each individual dimensions. Moreover, when adding multiple levels of dimensions, like platform / channel, it shows the "bar" only for lowest level, collapsing upper one leads to its disappearing.

I guess it's somehow related to the switch condition and HASONEVALUE, but have no sufficient experience to solve it. Please help.

Technical Limitation: It should be Excel. This is not so much about creating dashboards but rather providing auxiliary tools for current working tables. Trying to imply it in bare excel, including recalculating relative metrics, turns the table to something monstrous.

Answer 1

Totals do not have the context of the dimension so you'll need to use an aggregator like SUMX. Try something like:

plan :=
  SUMX(
    DISTINCT(dimensions_list[dimension]), 
    var _dimension = MAX(dimensions_list[dimension]) 
    return SWITCH(...) 
 )