PowerBI - расчёт среднего с помощью DAX
Очень часто, казалось бы, самая простая задача вызывает у нас затруднения в своём решении. Расчёт среднего как раз относится к таким. И по опыту могу сказать, что большая часть затруднений возникает из-за не понимания того, что же в действительности мы хотим усреднить. Поэтому ниже постараюсь разобрать как можно больше примеров стандартных вопросов о подсчёте среднего с точки зрения применения языка DAX в PowerBI.
Возьмём простую модель с одной таблицей фактов и тремя измерениями:
На основе данной модели создадим простейшую визуализацию в виде Матрицы, где в строках у нас будут названия товаров(ModelName), в столбцах месяца(NameMonth), а в значениях посчитаем сумму(SUM) продаж [Sum Orders Qty]
А теперь давайте попробуем посчитать для такой же визуализации среднемесячные продажи. Для этого воспользуемся функцией подсчёта среднего AVERAGE
Это явно не тот результат, который мы ожидали увидеть. Почему здесь везде единицы, хотя в таблице с суммой продаж минимальная продажа была 18? Всё очень просто - исходная таблица фактов содержит детальную информацию о продажах по каждому заказу, то есть имеет отличную гранулярность от той, что у нас в матрице.
Таким образом нам сначала необходимо рассчитать сумму продаж для каждого товара за месяц, а только потом уже производить усреднение на количество месяцев.
И вот начинающие аналитики идут привычным путём - делят сумму продаж на количество месяцев
Очевидно, что мы вновь получили не то, что ожидали. И если на уровне итогов, цифры стали похожи на правду, то на уровне месяца мы получаем значения значительно ниже, так как сумму продаж одного месяца мы делим на количество месяцев целого года. Плюс, пользователь может выбрать и меньшее количество месяцев и захочет увидеть среднемесячные продажи именно для этих месяцев.
Но мы помним то, о чём говорили в начале: решение в большей степени зависит от вопроса, который мы задаём и того, что нам требуется посчитать. Если нам нужно будет сравнить продажи текущего месяца со среднемесячными продажами, но всего года, тогда тут нам может понадобиться функция ALL
Давайте воспользуемся функционалом переменных и сделаем динамический подсчёт количества месяцев:
Теперь на уровне строк товаров мы видим корректные значения и в столбцах с месяцами и в столбце итогов. Но давайте ещё раз посмотрим на наши данные:
Как видно на скриншоте, таблица фактов содержит сведения о продажах товаров за несколько лет, а в матрице год ни как не учитывается. То есть получается, что сначала происходит суммирование продаж за все года, а потом делится на количество месяцев одного календарного года. Получается, что в действительности мера показывает не среднемесячные продажи, а некое среднее продаж за все года, которое сложно даже как-то назвать. Таким образом мы вновь столкнулись с проблемой гранулярности. И данная мера будет корректна только, если пользователь выберет в фильтре один год.
Но как же быть, если пользователю необходимо видеть среднемесячные продажи в разбивке по месяцам за несколько лет? Можно конечно добавить в нашу формулу ещё одну переменную, которая будет подсчитывать количество лет в контексте и далее использовать её как ещё один знаменатель
А можно и проще - изменить гранулярность переменной, добавив в неё год
Если вы внимательны, то сразу заметили, что на двух последних скриншотах различаются значения в столбце итогов, а все остальные значения идентичны. А всё потому, что в этом столбце нет контекста месяцев, а значит без фильтра по годам мы всегда будем получать значение 3. В то время как при подсчёте по столбцу StartMonth мы избегаем этой ошибки.
Но здесь мы подошли ещё к одному нюансу расчёта среднего, который зависит от того, что вы хотите посчитать. Разберём этот нюанс на примере одного товара Mountain-100
Как видно на скриншоте товар Mountain-100 продавался только в первые 6 месяцев 2015 года. Всего его было продано 219шт. А календарь содержит 30 периодов Год-Месяц. И вот здесь заключается разница в подходах: мы считаем фактические среднемесячные продажи или среднемесячные продажи за весь выбранный период?
Сейчас мера [Av Orders Qty Count Year-Month Dinamyc] показывает среднее за весь выбранный период, поэтому среднее значительно ниже фактических продаж. Чтобы рассчитать среднее фактических продаж, необходимо для подсчёта месяцев добавить контекст фактов:
Как видно на скриншоте значения сильно различаются.
Эту же самую задачу можно решить без использования переменных, если воспользоваться итеративной функцией AVERAGEX
Функция стала более лаконична и понятна: первым аргументом получаем таблицу нужной гранулярности, затем рассчитываем для каждой её строки значение, в данном случае сумму продаж, а затем усредняем полученные результаты.
На скриншоте специально показал версию функции AVERAGEX для подсчёта среднего за весь выбранный период. Просто добавив для каждой итерируемой строки в значение 0, так как простой подсчёт меры [Sum Orders Qty] добавляет контекст таблицы фактов.
Статья получилась достаточно объёмной, но надеюсь вы уловили мысль, что каждая рассмотренная функция имеет право считаться верной - всё зависит от вопроса, на который необходимо получить ответ!
Ссылка на файл с примерами функций.