DAX
September 9, 2021

#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.

Мера работающая с иерархией

Добавим меру в матрицу

Мера ISINSCOPE - на каждом уровне своя логика расчёта

Тот же самый результат можно получить с помощью функции HASONEVALUE

HASONEVALUE вместо ISINSCOPE
Мера HASONEVALUE - на каждом уровне своя логика расчёта

Подробное описание различий принципов работы этих двух функций лучше всего почитать в статье Альберто Феррари. Я же отмечу только, что функции ISINSCOPE до сих пор нет в моделях #PowerPivot в Excel, даже в Office365.

А также покажу два примера. Первый, показывает разный результат в зависимости от контекста:

Пример различия работы функций HASONEVALUE и ISINSCOPE

Я добавил на страницу срез с иерархией: год, месяц, день. И выбрал в срезе дни с 1 по 4 марта 2017 года. Мера [ISINSCOPE] считает по прежнему верно на каждом уровне, а вот мера [HASONEVALUE] на уровне года возвращает тот же расчёт, что и на уровне месяца, то есть среднедневную сумму продаж. Это происходит потому, что функция HASONEVALUE в данном контексте получает единственное значение по столбцу 'Calendar'[NameMonth], соответственно выполняется второе условие не зависимо от уровня иерархии, в отличии от ISINSCOPE.

Второй пример, если мы хотим в текстовое поле или в заголовок диаграммы вывести динамически меняющееся название:

Мера для заголовков

Какой бы я уровень в срезе не выбирал, значение не меняется, так как мера в карточке не находится на уровне иерархии

Заголовок ISINSCOPE

Частично эту задачу можно решить с помощью HASONEVALUE, но помните про ограничение, которое описано выше на примере внешнего контекста в матрице:

Заголовок HASONEVALUE

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

PS Ссылка посмотреть на отчёт. Ссылка скачать отчёт.

EDIT