Итак, суть такова. Я хочу внести в сводные таблицы некоторую легкость сравнения плана / факта.
Есть некоторое количество датасетов, в широком смысле устроенных похоже.
В них есть:
* даты (которые должны разворачиваться / схлопываться)
* разные *измерения* - платформы, регионы, каналы (по которым нужно группировать в разных комбинациях)
* измерение исходных данных pf: plan | fact
* абсолютные показатели - суммы - расходы, доходы, разные тематические количественные показатели. (рассматриваю как варианты, где они идут в отдельных столбцах, так и где они распивочены в один столбец "показатель")
* относительные показатели, задаваемые мерами (удельные значения, цены, коэффициенты).
Нужно: дополнительное измерение, которое было бы несложно тягать в разные места сводной, со значениями план / факт / отклонение % (абсолютное отклонение / что-то еще - по желанию).
(1) Пробовал такой подход: таблица aspects с 1 столбцом о четырех значениях: plan, fact, dif, ratio.
Втягивается в основную таблицу без создания связи.
Меры выглядят примерно так:
(2) Пробовал такой подход: таблица dimensions_list, со столбцом вида (рассмотрим на примере екома, сокращенно): expense|sales|clicks|orders|avg_order|roi|cr|ctr|cpc
Меры фигачу в два уровня: сперва лично каждый показатель
Абсолютные:
Относительные:
Затем - сборка
)
Тут у нас в роли нужного измерения экселевский автоблок Values
А ТЕПЕРЬ ВНИМАНИЕ ВОПРОС:
при обоих подходах "штанга", вставляемая из дополнительной таблицы - не попадает в total (а это одна из целей всего мероприятия). Ни в общий тотал по таблице, ни в subtotal по отдельному измерению.
Как сделать, чтобы это работало как нормальное "родное" измерение?
Техническое ограничение: нужен именно Excel. Это не столько рисование дашбордов, сколько вспомогательные вещи к рабочим таблицам (каковые при попытке учесть все вышеописанное (включая пересчет относительных показателей) приобретают какой-то монструозный облик.
Есть некоторое количество датасетов, в широком смысле устроенных похоже.
В них есть:
* даты (которые должны разворачиваться / схлопываться)
* разные *измерения* - платформы, регионы, каналы (по которым нужно группировать в разных комбинациях)
* измерение исходных данных pf: plan | fact
* абсолютные показатели - суммы - расходы, доходы, разные тематические количественные показатели. (рассматриваю как варианты, где они идут в отдельных столбцах, так и где они распивочены в один столбец "показатель")
* относительные показатели, задаваемые мерами (удельные значения, цены, коэффициенты).
Нужно: дополнительное измерение, которое было бы несложно тягать в разные места сводной, со значениями план / факт / отклонение % (абсолютное отклонение / что-то еще - по желанию).
(1) Пробовал такой подход: таблица aspects с 1 столбцом о четырех значениях: plan, fact, dif, ratio.
Втягивается в основную таблицу без создания связи.
Меры выглядят примерно так:
| Код |
|---|
expense:= 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") ,"dif", CALCULATE(SUM(combined[expense]),combined[pf]="plan")-CALCULATE(SUM(combined[expense]),combined[pf]="fact") ,"ratio", DIVIDE(CALCULATE(SUM(combined[expense]),combined[pf]="fact"),CALCULATE(SUM(combined[expense]),combined[pf]="plan"))-1) |
(2) Пробовал такой подход: таблица dimensions_list, со столбцом вида (рассмотрим на примере екома, сокращенно): expense|sales|clicks|orders|avg_order|roi|cr|ctr|cpc
Меры фигачу в два уровня: сперва лично каждый показатель
Абсолютные:
| Код |
|---|
_orders:=SUM(combined[orders]) |
| Код |
|---|
_avg_order:=DIVIDE([_sales], [_orders]) |
| Код |
|---|
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") ) fact:= // ... аналогично ratio:= =IF (ISBLANK([plan]) && ISBLANK([fact]),BLANK(), DIVIDE([fact],[plan])-1 |
Тут у нас в роли нужного измерения экселевский автоблок Values
А ТЕПЕРЬ ВНИМАНИЕ ВОПРОС:
при обоих подходах "штанга", вставляемая из дополнительной таблицы - не попадает в total (а это одна из целей всего мероприятия). Ни в общий тотал по таблице, ни в subtotal по отдельному измерению.
Как сделать, чтобы это работало как нормальное "родное" измерение?
Техническое ограничение: нужен именно Excel. Это не столько рисование дашбордов, сколько вспомогательные вещи к рабочим таблицам (каковые при попытке учесть все вышеописанное (включая пересчет относительных показателей) приобретают какой-то монструозный облик.
