План-факт-отклонение как отдельное измерение в DAX, Excel
У меня есть набор абсолютных показателей. Рассмотрим на примере екома. Допустим, это расходы на продвижение, выручка, заказы, показы и переходы. Они даются по разным месяцам и платформам. Данные имеются по плановым и фактическим значениям показателей, план и факт вынесены в столбец pf. Мне нужно
- Добавить относительные показатели. Как пример - конверсия (отношение заказов к переходам) или ROI (отношение выручки к расходам)
- Единообразно показывать в трех "аспектах", не знаю как грамотно назвать: план, факт, отклонение в %. (абсолютное отклонение - можно добавлять, можно нет, не принципиально).
Относительные показатели должны пересчитываться (месяц / весь год, одна платформа / все платформы), а не сводиться к среднему, поэтому они - точно идут в меры.
Измерение "план/факт/отклонение" хотелось бы сделать отдельным измерением (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