DAX
October 19, 2021

PowerBI - расчёт среднего с помощью DAX

Очень часто, казалось бы, самая простая задача вызывает у нас затруднения в своём решении. Расчёт среднего как раз относится к таким. И по опыту могу сказать, что большая часть затруднений возникает из-за не понимания того, что же в действительности мы хотим усреднить. Поэтому ниже постараюсь разобрать как можно больше примеров стандартных вопросов о подсчёте среднего с точки зрения применения языка DAX в PowerBI.

Возьмём простую модель с одной таблицей фактов и тремя измерениями:

Модель схемы "звезда"

На основе данной модели создадим простейшую визуализацию в виде Матрицы, где в строках у нас будут названия товаров(ModelName), в столбцах месяца(NameMonth), а в значениях посчитаем сумму(SUM) продаж [Sum Orders Qty]

Матрица продаж товаров по месяцам

А теперь давайте попробуем посчитать для такой же визуализации среднемесячные продажи. Для этого воспользуемся функцией подсчёта среднего AVERAGE

Матрица с функцией агрегирования AVERAGE

Это явно не тот результат, который мы ожидали увидеть. Почему здесь везде единицы, хотя в таблице с суммой продаж минимальная продажа была 18? Всё очень просто - исходная таблица фактов содержит детальную информацию о продажах по каждому заказу, то есть имеет отличную гранулярность от той, что у нас в матрице.

Структура таблицы фактов

Таким образом нам сначала необходимо рассчитать сумму продаж для каждого товара за месяц, а только потом уже производить усреднение на количество месяцев.

И вот начинающие аналитики идут привычным путём - делят сумму продаж на количество месяцев

Делим сумму продаж на константу месяцев

Очевидно, что мы вновь получили не то, что ожидали. И если на уровне итогов, цифры стали похожи на правду, то на уровне месяца мы получаем значения значительно ниже, так как сумму продаж одного месяца мы делим на количество месяцев целого года. Плюс, пользователь может выбрать и меньшее количество месяцев и захочет увидеть среднемесячные продажи именно для этих месяцев.

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

Давайте воспользуемся функционалом переменных и сделаем динамический подсчёт количества месяцев:

Динамический подсчёт количества месяцев

Теперь на уровне строк товаров мы видим корректные значения и в столбцах с месяцами и в столбце итогов. Но давайте ещё раз посмотрим на наши данные:

Один товар продавался в разные годы

Как видно на скриншоте, таблица фактов содержит сведения о продажах товаров за несколько лет, а в матрице год ни как не учитывается. То есть получается, что сначала происходит суммирование продаж за все года, а потом делится на количество месяцев одного календарного года. Получается, что в действительности мера показывает не среднемесячные продажи, а некое среднее продаж за все года, которое сложно даже как-то назвать. Таким образом мы вновь столкнулись с проблемой гранулярности. И данная мера будет корректна только, если пользователь выберет в фильтре один год.

Верхняя матрица показывает сумму продаж за 2016 год, а нижняя среднемесячные продажи

Но как же быть, если пользователю необходимо видеть среднемесячные продажи в разбивке по месяцам за несколько лет? Можно конечно добавить в нашу формулу ещё одну переменную, которая будет подсчитывать количество лет в контексте и далее использовать её как ещё один знаменатель

Добавили ещё одну переменную в знаменатель

А можно и проще - изменить гранулярность переменной, добавив в неё год

Расчёт среднего по столбцу StartMonth

Если вы внимательны, то сразу заметили, что на двух последних скриншотах различаются значения в столбце итогов, а все остальные значения идентичны. А всё потому, что в этом столбце нет контекста месяцев, а значит без фильтра по годам мы всегда будем получать значение 3. В то время как при подсчёте по столбцу StartMonth мы избегаем этой ошибки.

Но здесь мы подошли ещё к одному нюансу расчёта среднего, который зависит от того, что вы хотите посчитать. Разберём этот нюанс на примере одного товара Mountain-100

Динамика продаж товара Mountain-100

Как видно на скриншоте товар Mountain-100 продавался только в первые 6 месяцев 2015 года. Всего его было продано 219шт. А календарь содержит 30 периодов Год-Месяц. И вот здесь заключается разница в подходах: мы считаем фактические среднемесячные продажи или среднемесячные продажи за весь выбранный период?

Сейчас мера [Av Orders Qty Count Year-Month Dinamyc] показывает среднее за весь выбранный период, поэтому среднее значительно ниже фактических продаж. Чтобы рассчитать среднее фактических продаж, необходимо для подсчёта месяцев добавить контекст фактов:

Среднее в контексте фактов

Как видно на скриншоте значения сильно различаются.

Эту же самую задачу можно решить без использования переменных, если воспользоваться итеративной функцией AVERAGEX

Среднее с помощью функции итерации

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

На скриншоте специально показал версию функции AVERAGEX для подсчёта среднего за весь выбранный период. Просто добавив для каждой итерируемой строки в значение 0, так как простой подсчёт меры [Sum Orders Qty] добавляет контекст таблицы фактов.

Статья получилась достаточно объёмной, но надеюсь вы уловили мысль, что каждая рассмотренная функция имеет право считаться верной - всё зависит от вопроса, на который необходимо получить ответ!

Ссылка на файл с примерами функций.