#DAX - изменение расчета в зависимости от уровня иерархии или в итогах
В этой статье описывается несколько приёмов изменения расчёта в зависимости от уровня иерархии в #PowerBI, такие как: изменение гранулярности и использование функций HASONEVALUE и ISINSCOPE.
Одна из наиболее часто встречаемых иерархий - это: дата, месяц, год. Возьмём её для примера и сформулируем задачу:
Необходимо создать визуальный элемент матрица(сводная таблица), в котором в строках будет находиться иерархия: дата, месяц, год, - а в значениях на уровне дня рассчитывается сумма продаж, на уровне месяца среднедневная сумма продаж, а на уровне года и выше максимальная сумма дневной продажи.
Модель состоит из четырех таблиц: факты(Sales) и справочники(Customers, Products, Calendar).
Таблица фактов имеет гранулярность: дата, покупатель, заказ, товар
Самая простая задача это рассчитать сумму продаж за день. Для этого создадим простую меру, которая будет суммировать значения по столбцу OrderQuantity:
Затем создадим визуализацию матрица, добавим даты и меру:
Далее нам необходимо добавить уровни месяц и год, но изменить для них логику расчёта на среднедневную и максимальную за день соответственно:
Но, как видно на рисунке "Матрица с иерархией" для уровня месяц и год какие-то очень маленькие значения, да и для уровня даты тоже, если честно.
Это произошло потому, что я взял простые агрегирующие меры по столбцу: AVERAGE и MAX соответственно.
Но напомню, что таблица фактов имеет гранулярность: дата, покупатель, заказ, товар, - то есть усредняются значения каждой строки таблицы фактов, а не сумма продаж за день, которая складывается из продаж нескольким покупателям и нескольких товаров. То есть, чтобы получить среднедневную продажу, необходимо сначала рассчитать сумму продаж на каждый день, а только затем выполнять усреднение или поиск максимального значения.
Для этого нам подойдут функции итерации, которые очень похожи на обычные функции агрегации, но с буквой X на конце. В нашей задаче мы будем использовать функции AVERAGEX и MAXX соответственно.
Итерационные функции сначала рассчитывают выражение для каждой строки, а потом агрегируют полученный результат. Так как нам необходимо производить все вычисления относительно дня, то нам понадобится таблица с такой гранулярностью. И здесь прекрасно подходит таблица Calendar, но, чтобы не тянуть все поля из этой таблицы - календарь может содержать несколько десятков столбцов, а нам нужна только дата, - вместо ссылки на таблицу используем табличное выражение VALUES, которое вернёт список дат из столбца 'Calendar'[Date].
Будьте внимательны, когда используете табличные выражения в итерационных функциях для изменения гранулярности расчёта. Например, два табличных выражения VALUES('Calendar'[NumberDay]) и VALUES('Calendar'[Date]), которые должны давать гранулярность день вернут одинаковый результат только в контексте одно года и одного месяца, т.к. номер дня - это число от 1 до 31, соответственно вы получите таблицу максимум из 31 строки, тогда как дата сама по себе несёт контекст номера месяца и номера года, поэтому в таблице будет столько строк, сколько дат в выбранном периоде.
Добавив новые меры в матрицу мы видим, что теперь на уровне месяца и года мы получаем другой тип вычисления, нежели на уровне дня.
За счёт того, что на уровне даты в контексте вычисления всегда одна строка и функция усреднения, и функция максимума возвращают значения равные сумме продаж за день. Но, у нас по прежнему две функции, а мы хотим, чтобы одна функция выполняла разный расчёт на разных уровнях. Поэтому создадим ещё одну функцию, которая сначала будет проверять на каком уровне иерархии производится расчёт, а затем выполнять соответствующее вычисление.
Так как уровней у нас несколько, то здесь лучше всего подойдёт функция SWITCH, которая выполняет проверку нескольких условий по порядку. А для проверки уровня иерархии будем использовать функцию ISINSCOPE.
Тот же самый результат можно получить с помощью функции HASONEVALUE
Подробное описание различий принципов работы этих двух функций лучше всего почитать в статье Альберто Феррари. Я же отмечу только, что функции ISINSCOPE до сих пор нет в моделях #PowerPivot в Excel, даже в Office365.
А также покажу два примера. Первый, показывает разный результат в зависимости от контекста:
Я добавил на страницу срез с иерархией: год, месяц, день. И выбрал в срезе дни с 1 по 4 марта 2017 года. Мера [ISINSCOPE] считает по прежнему верно на каждом уровне, а вот мера [HASONEVALUE] на уровне года возвращает тот же расчёт, что и на уровне месяца, то есть среднедневную сумму продаж. Это происходит потому, что функция HASONEVALUE в данном контексте получает единственное значение по столбцу 'Calendar'[NameMonth], соответственно выполняется второе условие не зависимо от уровня иерархии, в отличии от ISINSCOPE.
Второй пример, если мы хотим в текстовое поле или в заголовок диаграммы вывести динамически меняющееся название:
Какой бы я уровень в срезе не выбирал, значение не меняется, так как мера в карточке не находится на уровне иерархии
Частично эту задачу можно решить с помощью HASONEVALUE, но помните про ограничение, которое описано выше на примере внешнего контекста в матрице:
Оказывается не так сложно изменить расчёт итогов или расчёт на разных уровнях иерархии. Главное помнить о контексте вычисления и выбирать решение, которое будет работать правильно именно в этом визуальном элементе.