Пять мер DAX в Power BI, без которых не работает дашборд KPI ритейла

Что такое меры DAX в Power BI

DAX (Data Analysis Expressions) — язык формул Power BI, Power Pivot в Excel и Analysis Services. На нём пишут меры, вычисляемые столбцы и таблицы. Для отчётов важнее всего меры: они не хранятся в модели, а считаются в момент построения визуала с учётом всех фильтров — выбранного периода, рынка, категории, магазина.

Простейшая мера — Продажи = SUM ( Sales[Amount] ). Функция SUM уже учитывает фильтры отчёта: в матрице по категориям она покажет сумму каждой категории, в карточке с фильтром по рынку — сумму этого рынка. Когда фильтр нужно изменить внутри формулы, например посчитать только продажи по полной цене, используют CALCULATE. Эта функция вычисляет выражение в изменённом контексте фильтров и встречается почти в каждой нетривиальной мере.

Контекст фильтров — ключевое понятие DAX. Представьте матрицу: в строках категории, в столбцах недели, сверху срез по рынку. Для каждой ячейки Power BI собирает свой набор фильтров — «трикотаж, неделя 12, рынок А» — и вычисляет меру заново. Поэтому одна мера даёт правильный результат и в ячейке, и в итоге строки, и в итоге всей таблицы, если её формула делит суммы, а не складывает готовые проценты.

Создать меру можно на вкладке «Моделирование» → «Создать меру» или через контекстное меню таблицы в панели данных. Я собираю все меры в отдельной пустой таблице мер и раскладываю их по папкам отображения: «Продажи», «Запасы», «Маржа». Когда мер становится несколько десятков, это экономит время всем, кто работает с моделью.

Мера DAX или вычисляемый столбец

Вычисляемый столбец считается один раз для каждой строки при обновлении модели и занимает память. Мера считается на лету для агрегированных данных. Правило, которым я пользуюсь: если значение описывает одну строку и его можно использовать как фильтр или ось — это столбец. Если это доля, процент или коэффициент — это мера.

Пример из ритейла: признак «продано по полной цене» — это свойство конкретной продажи, его удобно сделать столбцом. А доля продаж по полной цене — мера, потому что проценты нельзя складывать. Если посчитать маржинальность столбцом и вывести её в матрицу, Power BI просуммирует проценты строк, и итог по категории окажется бессмысленным.

Меры ниже написаны для типовой модели «звезда»: таблица продаж Sales, снимки остатков Stock, план продаж SalesPlan, справочник товаров Product и таблица дат 'Date', помеченная как таблица дат. Базовые меры — Net Sales (выручка без НДС за вычетом возвратов) и Net Units (штуки за вычетом возвратов) — используются во всех остальных.

Net Sales =
SUM ( Sales[NetAmount] )

Net Units =
SUM ( Sales[Quantity] ) - SUM ( Sales[ReturnedQuantity] )

Мера DAX для покрытия запаса

Покрытие запаса показывает, на сколько недель хватит текущего остатка при темпе продаж последних четырёх недель. Это взгляд назад: мы исходим из того, что покупатели будут покупать так же, как покупали недавно. Остаток — полуаддитивная величина: по товарам и магазинам его суммируют, а по времени берут последний снимок периода.

Stock Units =
CALCULATE (
    SUM ( Stock[Units] ),
    LASTNONBLANK ( 'Date'[Date], CALCULATE ( SUM ( Stock[Units] ) ) )
)

Avg Weekly Units L4W =
VAR LastDay = MAX ( 'Date'[Date] )
RETURN
    DIVIDE (
        CALCULATE ( [Net Units], DATESINPERIOD ( 'Date'[Date], LastDay, -28, DAY ) ),
        4
    )

Stock Cover Weeks =
DIVIDE ( [Stock Units], [Avg Weekly Units L4W] )

Четыре недели — это правило большого пальца, а не стандарт: окно достаточно длинное, чтобы сгладить случайные всплески, и достаточно короткое, чтобы заметить смену темпа. Для новинок с продажами меньше четырёх недель мера занизит средний темп, поэтому такие модели я смотрю отдельно.

Доля продаж по полной цене в DAX

Доля продаж по полной цене показывает, какая часть выручки получена без скидок. В fashion-ритейле это один из главных показателей качества сезона: высокая выручка при низкой доле полной цены означает, что товар продавался в основном на распродаже. Для меры нужен признак в таблице продаж — вычисляемый столбец или поле из источника, сравнивающее фактическую цену с первой ценой.

Full Price Sales =
CALCULATE ( [Net Sales], Sales[IsFullPrice] = TRUE () )

Full Price Share % =
DIVIDE ( [Full Price Sales], [Net Sales] )

Условный пример: категория продала на 500 тысяч, из них на 350 тысяч по полной цене. Доля полной цены — 70%. Если в прошлом сезоне к той же неделе она была 80%, скидки начались раньше или были глубже, и маржа категории почти наверняка ниже плана. Поэтому в дашборде я ставлю эту меру рядом с маржинальностью.

Процент возвратов в DAX

Процент возвратов считают в штуках от проданного количества. Для онлайн-канала показатель особенно важен: возврат приходит через одну–три недели после продажи, и отчёт по неделе продаж без учёта возвратов выглядит лучше, чем есть на самом деле.

Gross Units Sold =
SUM ( Sales[Quantity] )

Returned Units =
SUM ( Sales[ReturnedQuantity] )

Returns Rate % =
DIVIDE ( [Returned Units], [Gross Units Sold] )

Если данные позволяют, привязывайте возврат к дате исходной продажи, а не к дате возврата. Тогда процент возвратов по неделе продаж будет расти ещё несколько недель, зато покажет реальную долю возвращённого товара из каждой недели, а не смесь разных периодов.

Недели запаса в DAX

Недели запаса похожи на покрытие, но смотрят вперёд: текущий остаток делится не на прошлые продажи, а на плановые или прогнозные продажи ближайших недель. Для сезонного товара разница заметна. Перед пиком сезона покрытие по прошлым продажам покажет десять недель, а по плану — четыре, потому что спрос вот-вот вырастет. В разных компаниях эти термины используют по-разному, поэтому в дашборде я всегда подписываю, на какие продажи делится остаток.

Weeks of Supply =
VAR LastDay = MAX ( 'Date'[Date] )
VAR PlanNext4W =
    CALCULATE (
        SUM ( SalesPlan[Units] ),
        DATESINPERIOD ( 'Date'[Date], LastDay + 1, 28, DAY )
    )
RETURN
    DIVIDE ( [Stock Units], DIVIDE ( PlanNext4W, 4 ) )

Читать меру удобно против целевого диапазона категории. Если недель запаса меньше целевого минимума, пора дозаказывать или перемещать товар. Если больше максимума, в категории лишний запас, и бюджет закупок на следующие месяцы стоит сократить.

Маржа в DAX

Валовая маржа — разница между выручкой без НДС и себестоимостью проданного товара. Себестоимость единицы обычно лежит в справочнике товаров, поэтому её умножают на количество построчно с помощью итератора SUMX. Маржинальность в процентах — отдельная мера, которая делит маржу на выручку.

COGS =
SUMX ( Sales, ( Sales[Quantity] - Sales[ReturnedQuantity] ) * RELATED ( Product[UnitCost] ) )

Gross Margin =
[Net Sales] - [COGS]

Gross Margin % =
DIVIDE ( [Gross Margin], [Net Sales] )

Себестоимость лучше брать ту, по которой товар учтён в финансовой отчётности, иначе маржа в дашборде не совпадёт с цифрами финансовой команды. Если себестоимость меняется со временем, храните её в таблице продаж на момент продажи, а не в справочнике товаров. Маржинальность, как и все доли, работает только как мера: итог по категории получится из суммы маржи и суммы выручки, а не из среднего процентов по моделям.

Прежде чем показывать меру команде, я проверяю её в Excel. Выгрузите исходные строки за один срез, например одну категорию за одну неделю, посчитайте показатель вручную и сравните с мерой в таблице Power BI с тем же фильтром. Второй способ — «Анализ в Excel»: сводная таблица подключается к модели, и меру можно поставить рядом с суммами, посчитанными формулами. Если цифры расходятся, причина почти всегда в связях модели или в пустых строках справочников.

Как эти пять мер складываются в одну страницу для руководителя, я описываю в статье о дашборде продаж в Power BI и Excel.