Группы вычислений в Power BI
В этой статье я хотел бы наглядно и на живом примере показать, как можно упростить и ускорить расчёты в Microsoft Power BI с помощью групп вычислений (calculation groups) и параметров полей (field parameters). Само собой, я предполагаю, что вы не полный ноль в Power BI и, как минимум, умеете загружать туда данные, связывать их в модель и писать по ним простые меры на DAX. Если с этим пока сложно, то рекомендую ознакомиться сначала с этой статьей и видеоуроком.
Постановка задачи
Итак, предположим, что мы загрузили в Power BI и связали в модель три простых таблицы с данными:
- таблицу фактов Sales с данными о продажах за 2020-2021 гг., где есть стоимость, вес, количество штук и прочие детали по каждому заказу;
- таблицу-справочник Customers с данными о покупателях (связана с Sales по уникальному ID клиента);
- календарную таблицу дат Dates - must have в любой модели данных (связана с Sales по дате продажи).
Затем мы создали три простейших базовых меры для подсчёта итогов по стоимости, весу и количеству штук:
Вес = SUM( Sales[Вес] )
Стоимость = SUM( Sales[Стоимость] )
Штуки = SUM( Sales[Штук] )
Если в целях проверки поместить их, например, в визуализацию матрицы с годами и месяцами, то это будет выглядеть примерно так:
Теперь представим, что от мудрого руководителя нам прилетает вводная: хочу видеть в похожей таблице с годами и месяцами более подробную аналитику по стоимости, например:
- среднюю стоимость на клиента
- стоимость нарастающим итогом с начала года (YTD)
- долю стоимости текущего месяца внутри года
Т.е. на первый взгляд нужно садиться и писать ещё три меры, используя в них базовую меру стоимости:
Стоимость на клиента = AVERAGEX( Customers, [Стоимость] )
Стоимость YTD = TOTALYTD( [Стоимость], Dates[Дата] )
Доля месяца в году по стоимости = DIVIDE( [Стоимость], CALCULATE( [Стоимость], REMOVEFILTERS( Dates[Месяц], Dates[Номер месяца] ) ) )
Потом их можно будет закинуть в матрицу и получить желаемое.
Но где гарантия, что увидев такую красоту, ваш руководитель не скажет: "О, здорово! А теперь сделай мне такие же расчёты, но по весу товаров. И по количеству штук тоже давай сделаем!"
Если решать задачу «в лоб», то для этого нам придётся для этого создать еще 6 мер – дважды повторить каждую из 3 мер, заменяя там последовательно исходную меру выручки на меру прибыли и меру количества проданных товаров. Очевидно, что это чисто механическая, скучная работа, которая, к тому же, забивает нашу модель кучей однообразных мер. А если базовых мер будет не три, как в нашем простом примере, а сильно больше? Тоска зелёная.
На самом деле для решения подобной задачи можно создать группу вычислений (calculation group) – набор шаблонных формул, в которые затем автоматически будет подставляться любая выбранная пользователем мера: хочешь - стоимость, хочешь - вес, хочешь - штуки.Создание группы вычислений
В старых версиях Power BI для работы с группами вычислений приходилось использовать сторонние приложения, например, бесплатную надстройку Tabular Editor. Начиная же с ноябрьского обновления 2023 года инструменты для работы с группами вычислений появились и во встроенном функционале Power BI.
Переключитесь в Представление модели (Model view) и на панели Данные (Data) справа перейдите на вкладку Модель (Model). В древовидном списке содержимого должен быть пункт Группы вычислений (Calculation Groups):
Щёлкните правой кнопкой мыши по строке Группы вычислений и выберите команду Новая группа вычислений (New calculation group). К сожалению, группы вычислений «не дружат» с неявными мерами (и это ещё одна причина, чтобы их не использовать), о чём мы получим честное предупреждение, с которым надо будет согласиться:
Давайте сразу переименуем нашу группу, сделав по ней двойной щелчок левой кнопкой мыши, и назовём её, например, Аналитика. Соседний Столбец группы вычислений (Calculation group column) аналогичным образом тоже переименуем, например, в Метрики.
После этого можно переходить непосредственно к созданию элементов вычислений – наших четырёх шаблонных формул, входящих в группу. Для этого щёлкнем правой кнопкой мыши уже по строке Элементы расчета (Calculation Items), выберем команду Новый элемент вычисления (New calculated item), а затем введём выражение для его определения в строку формул.
Для начала давайте создадим элемент, который просто ссылается на базовую меру:
Итого =SELECTEDMEASURE ()
Здесь функция SELECTEDMEASURE() используется как заполнитель – вместо неё позднее, при использовании группы вычислений в отчёте, будет автоматически подставляться любая используемая в текущий момент мера, будь то стоимость, количество проданных товаров, вес и т. д.
Следующие три меры создаются совершенно аналогично:
В среднем на клиента =
AVERAGEX ( Customers, SELECTEDMEASURE () )
YTD =
TOTALYTD ( SELECTEDMEASURE (), Dates[Дата] )
Доля в году =
DIVIDE (
SELECTEDMEASURE (),
CALCULATE (
SELECTEDMEASURE (),
REMOVEFILTERS ( Dates[Месяц], Dates[Номер месяца] )
)
)
В итоге, если все сделали правильно, у нас должна получиться вот такая картина:
Настройка форматов
По умолчанию вычисляемые элементы наследуют числовой формат базовой меры. Если же хочется задать его для какого-то элемента индивидуально, то это можно сделать с помощью динамического формата. В нашем случае это совершенно необходимо для последнего элемента Доля в году, т. к. он должен отображаться в процентах.
Чтобы это реализовать, выберем требуемый вычисляемый элемент на вкладке Модель (Model) в панели Данные (Data), а затем на соседней панели Свойства (Properties) включим опцию Строка динамического формата (Dynamic format string). После этого можно щёлкнуть ниже по ссылке Изменить в строке формул DAX (Edit in DAX formula bar) и ввести туда маску нашего пользовательского формата, состоящую из трёх фрагментов, разделённых точкой с запятой:
"+0.0%;-0.0%;0%"
Принципы тут те же, что и при создании пользовательских форматов для мер. Первый фрагмент (+0.0%) будет применяться к положительным значениям отклонений и отображать их в виде процентов со знаком плюс и одним дробным разрядом. Второй (-0.0%) – делать то же самое, но со знаком минус для отрицательных значений. Третий (0%) будет применяться только к нулевым отклонениям.
Для вывода обычных процентов независимо от знака (как нам и нужно в Доле в году) можно ввести только первый фрагмент, т.е.
"0.0%"
Выводим результаты
Осталось воспользоваться созданной и настроенной группой вычислений в нашем дашборде. Для этого создадим матрицу, где:
- в области строк будут лежать те же годы и месяцы;
- в область столбцов поместим столбец Метрики из нашей группы Аналитика;
- в область значений можно положить любую меру, которая должна быть использована как базовая мера для нашей группы вычислений – например, ту же стоимость.
В результате получаем:
А если закинуть в область значений вместо стоимости, например, штуки, то получим аналогичную картину, но уже для них и т.д.
И не требуется создавать никаких дополнительных однообразных мер. Удобно.
Добавляем срез-переключатель для базовой меры
Если же хочется сделать совсем красиво, то можно сделать срез-переключатель, где наш руководитель сам сможет выбирать меру (стоимость, вес или штуки), которую он хочет видеть в отчёте.
Для этого:
- Выбираем на вкладке Моделирование (Modeling) команду Создание параметра - Поля (Create parameter - Field).
- Закидываем в него все базовые меры (Стоимость, Вес, Штуки).
- Проверяем, чтобы внизу окна была включена галочка Добавить срез на страницу (Add slicer to page) и жмём кнопку Создать (Create).
Останется заменить в созданной ранее матрице в области значений лежащую там меру на только что сделанный параметр - и можно будет переключаться между базовыми мерами в группе вычислений при помощи удобного среза:
Ссылки по теме
- Основы работы в Microsoft Power BI
- Хитрости сортировки в Power BI
- Как сравнить 2 любых объекта (товара, периода и т.д.) в Power BI
А можно ли внутри группы вычислений, создавая новый элемент вычисления, ссылаться на уже имеющийся элемент вычисления? Есть ощущение, что я когда-то читал, что можно, но это было еще до появления встроенного в PBI инструмента, и речь 100% шла о Tabular Editor. Недавно понадобилась такая возможность, но беглый поиск результатов не дал.
Можно сослаться, используя таблицу, которая формируется при создании группы вычислений. Например, на вычисляемый элемент YTD можно сослаться как
Можно ли поменять порядок расположения метрик в матрице?
Например, чтобы первый столбец был "Итого", а потом "В среднем на клиента".
Будет здорово, если вы будете больше уроков. Или выпустите книгу о microsoft fabric / bi.
Подскажите, как форматировать числовые значения параметра в значениях?
Обычно щелчком правой кнопки можно сделать условное форматирование, сейчас - нет такой возможности.
Попробовал в визуализации сделать в значениях задать условное форматирование по правилу, но тоже нет такого меню - только выбор цвета.
Я хотел сделать градацию к примеру: 1) значения >100% -красным 2) значения меньше 100% - зелёным и пр.
Причём это нужно не для всех метрик, а только для части