Деметра Холдинг — объединение продаж и стоков с пересчётом средневзвешенного протеина

Условие

Деметра — крупный экспортёр зерна. Дано:

• Продажи.xlsx — таблица продаж по портам с указанием объёма (main_values) и качества (protein).
• Стоки и плановый завоз.xlsx — что уже на стоке + плановое поступление.

Задачи:

1. Объединить «Продажи» и «Стоки и плановый завоз» в одну сводную (пример «убитой сводной таблицы»):

2. Посчитать средневзвешенный протеин для продаж и стоков (вес = объём в тоннах).

3. Сохранить связи и DAX-формулы в Power BI Pivot.

Решение

Структура подхода (Power BI / DAX)

Источники:

• sales(grain, port, basis, batch_id, dt_unloading, protein, volume) — продажи (отрицательные значения).
• stock(grain, port, basis, dt, protein, volume) — стоки и плановое.

Объединение через дополнительную dimension table «KPI» (в Power BI можно создать через DAX UNION с добавлением колонки kpi_type):

combined =
UNION(
  SELECTCOLUMNS(sales, "grain", [grain], "port", [port], "month", [dt_unloading],
                "kpi", "Продажи, тн", "value", [volume],
                "vw_protein", [protein] * [volume]),
  SELECTCOLUMNS(stock, "grain", [grain], "port", [port], "month", [dt],
                "kpi", "Стоки и плановый завоз, тн", "value", [volume],
                "vw_protein", [protein] * [volume])
)

Меры

Volume   = SUM(combined[value])
 
VW_Protein =
DIVIDE(
  SUM(combined[vw_protein]),
  SUM(combined[value])
)

Для продаж (отрицательные value) формула должна работать корректно: Σ(volume × protein) / Σ(volume) дает корректный взвешенный protein даже при минусах (математически).

Тонкость: если volume отрицательный, можно взять ABS(value) для веса, чтобы интуитивно совпадало.

Альтернатива: SQL

WITH combined AS (
  SELECT grain, port,
         DATE_TRUNC('month', dt_unloading) AS mth,
         'Продажи, тн' AS kpi,
         volume,
         protein * volume AS pv
  FROM sales
  UNION ALL
  SELECT grain, port,
         DATE_TRUNC('month', dt) AS mth,
         'Стоки и плановый завоз, тн' AS kpi,
         volume,
         protein * volume AS pv
  FROM stock
)
SELECT grain, port, mth, kpi,
       SUM(volume) AS volume,
       SUM(pv) / NULLIF(SUM(volume), 0) AS vw_protein
FROM combined
GROUP BY grain, port, mth, kpi
ORDER BY grain, port, mth, kpi;

Pivot

В Power BI или Excel:

• Строки: grain, port, kpi.
• Столбцы: mth.
• Значения: volume (для kpi Продажи, тн / Стоки) и vw_protein (для kpi с протеином).

Проверка / интерпретация

• Sanity: Σ Продажи + Σ Стоки ≠ 0 (продажи отрицательные, стоки положительные).
• Средневзвешенный protein на порт должен быть в диапазоне 9–15 для wheat.
• Σ(volume × protein) / Σ(volume) — корректная формула (массив-средний с весами).

Подводные камни

1. Отрицательные продажи: для веса лучше ABS(volume), иначе при суммировании со стоком знак мешает.
2. Разные периоды: продажи бывают в Sep23, Oct23, Nov23, стоки — на конкретную дату. Если дата разная, нужно decide, как агрегировать.
3. Несколько protein на одну позицию — нужно выбрать актуальный (последний или из спеки).
4. Power Query запрещён в задании — все преобразования через DAX. UNION в DAX через UNION() функцию, а не через relationship.
5. NULL в protein: в vw_protein = protein × volume NULL даст NULL — отфильтруйте перед агрегацией или используйте COALESCE.
6. Связки grain-port-period должны совпадать между sales и stock — иначе матрица будет «дырявой».

Эталонный ответ

DAX UNION sales и stock в combined-table → Volume через SUM, VW_Protein через Σ(p × v) / Σ(v). Pivot по rows (grain, port, kpi), columns — month. Проверка: вес — abs(volume); средний протеин в правильном диапазоне.