План-факт-отклонение как отдельное измерение в DAX, Excel

У меня есть набор абсолютных показателей. Рассмотрим на примере екома. Допустим, это расходы на продвижение, выручка, заказы, показы и переходы. Они даются по разным месяцам и платформам. Данные имеются по плановым и фактическим значениям показателей, план и факт вынесены в столбец pf. Мне нужно

  1. Добавить относительные показатели. Как пример - конверсия (отношение заказов к переходам) или ROI (отношение выручки к расходам)
  2. Единообразно показывать в трех "аспектах", не знаю как грамотно назвать: план, факт, отклонение в %. (абсолютное отклонение - можно добавлять, можно нет, не принципиально).

Относительные показатели должны пересчитываться (месяц / весь год, одна платформа / все платформы), а не сводиться к среднему, поэтому они - точно идут в меры.

Измерение "план/факт/отклонение" хотелось бы сделать отдельным измерением (dimension), чтобы можно было его тягать в разные места сводной таблицы - в строки, столбцы, над параметрами, под параметрами и пр.

Ограничение: нужен эксель, поэтому строить промежуточные таблицы в даксе напрямую - не получится (а запихивания их в каждую меру - хотелось бы максимально избежать).

"Простая" эксельная таблица на формулах и группировках превращается в монстра франкенштейна, охота чего-то более компактного и управляемого.

Что пробовал:

  • сделать микротаблицу aspects об одном столбце и трех значениях: plan / fact / ratio
  • и тупо-претупо ее вставлять в сводную. При этом все параметры считать в таком стиле:
=SWITCH(IF(HASONEVALUE(aspects[aspect]),VALUES(aspects[aspect]),BLANK())
,"plan", CALCULATE(SUM(combined[expense]),combined[pf]="plan")
,"fact", CALCULATE(SUM(combined[expense]),combined[pf]="fact")
,"ratio", IFERROR(CALCULATE(SUM(combined[expense]),combined[pf]="fact")/CALCULATE(SUM(combined[expense]),combined[pf]="plan")-1,BLANK())
)

При этом а) ДАКС ругается, мол установите связь между таблицами (это раздражает) б) происходят всякие недобрые эффекты, вроде неадекватного поведения дат (пропадают с концами свернутые годы) или показывания 100% в ratio для заведомо пустых ячеек.

Я чувтствую, что верный путь где-то рядом, но не могу сообразить, где заблудился.

Кусок конечной таблицы, как он есть сейчас

            24-01               
platform    channel_en  parameter   share   plan    fact    dif ratio
platform_1  channel_1   Расход  0.09154743  5000    6259.41 1259.41 0.251882
platform_1  channel_1   Выручка 0.083232382 10000   23257.95    13257.95    1.325795
platform_1  channel_1   ROI     2   3.715677676 1.715677676 0.857838838
platform_1  channel_1   Переходы    0.044150325 2746    5901    3155    1.148943918
platform_1  channel_1   Показы  0.049034903 91533   143287  51754   0.565413567
platform_1  channel_1   Заказы  0.168721461 357 739 382 1.070028011
platform_1  channel_1   Ср.заказ        28.01120448 31.47219215 3.46098767  0.12355726
platform_1  channel_1   Конверсия       0.130007283 0.125233011 -0.004774272    -0.036723112
platform_1  total   Расход  0.294623451 15000   20144.41    5144.41 0.342960667
platform_1  total   Выручка 0.169864674 25000   47465.95    22465.95    0.898638
platform_1  total   ROI     1.666666667 2.356283952 0.689617285 0.413770371
platform_1  total   Переходы    0.163627794 10246   21870   11624   1.134491509
platform_1  total   Показы  0.480231802 591533  1403306 811773  1.372320733
platform_1  total   Заказы  0.33630137  732 1473    741 1.012295082
platform_1  total   Ср.заказ        34.15300546 32.22399864 -1.929006822    -0.05648132
platform_1  total   Конверсия       0.071442514 0.067352538 -0.004089976    -0.057248495

Ответы (0 шт):