Всем привет,
Меня зовут Кажденова Асия из mybi connect и в данной статье мы рассмотрим формирование “Сравнения месяцев” из шаблонного Отчета по продажам для GetCourse, который показался наиболее интересным с точки зрения построения визуализаций. Для построения этой страницы мы использовали стандартные визуальные элементы Power BI Desktop и внешний инструмент – Tabular Editor. Напомним, что отчет построен на данных из GetCourse, полученных с помощью нашего сервиса mybi connect.
Здесь находится разбор всех листов шаблонного отчета по GetCourse в видео формате и в частности – сравнение месяцев.
Страницу “Сравнение месяцев” можно посмотреть по трем показателям: заработанная сумма, количество заказов и количество менеджеров. Каждый показатель активируется нажатием соответствующей кнопки и вызывает действие перемещения по закладкам. Так как содержание закладок практически идентичны, разберем построение дашборда с закладки “Сумма”:
Модель
В одной из прошлый статей мы уже рассматривали процесс построения модели данных на основании выгрузки mybi connect из GetCourse. Для данного отчета нам потребуются только часть таблиц
Сущности, на основе которых мы будем строить расчеты – это ‘GC Заказы’, ‘GC Параметры заказов’, ‘SHD Связь календарей’ и ‘SHD Параметры дат’:
Срезы
Для построения срезов по Менеджерам и Статусам используются соответствующие поля из сущности ‘GC Параметры заказов’, а Месяц – из ‘SHD Параметры дат’. При этом, для Месяца, в Форматировании визуального элемента – Параметры среза – Выбор, включим Единичный выбор. Выбрав один месяц, например октябрь 2023 года, отчет будет отображать информацию за текущий месяц – октябрь 2023 и предыдущий – сентябрь 2023 года.
Для Отображения заказов создадим отдельно стоящую таблицу Расчет заказов, состоящую из двух строк: по дате создания заказа и по дате закрытия.
Расчет заказов = UNION ( ROW ( "Тип расчета заказа", "по дате создания", "Порядок", 1 ), ROW ( "Тип расчета заказа", "по дате закрытия", "Порядок", 2 ) )
Данная таблица отдельно стоящая, т.е. ни с одной другой таблицей в модели она не имеет связей. Обращаться к ней мы будем на уровне DAX-формул.
Срезы, как и карточки являются сквозными и будут отображаться на всех закладках данного листа, т.е. при переключении между показателями они должны иметь пиктограмму “отображения визуального элемента”. Для настройки визуальных элементов на закладках, активируйте панели Закладки и Выбор в меню Представление:
Карточки
Для оформления карточек и графиков ниже, нам потребуется установленная программа Tabular Editor. Её вы можете скачать с официального сайта https://tabulareditor.com/downloads. После установки в Power BI Desktop у вас появится вкладка Внешние инструменты, Tabular Editor:
Tabular Editor — это инструмент, который предоставляет расширенные возможности управления моделью данных в Power BI. С его помощью можно более гибко управлять структурой модели данных, создавать и редактировать таблицы, столбцы, меры и связи непосредственно в редакторе кода. Этот инструмент предоставляет более продвинутый и гибкий подход к управлению моделью данных, чем стандартные средства Power BI Desktop. Также он позволяет автоматизировать многие процессы, такие как создание мер и таблиц, что может значительно ускорить разработку и обслуживание моделей данных в Power BI. При запуске Tabular Editor с ленты Внешние инструменты он автоматически подключится к .pbix файлу, с которым вы работаете.
Главное окно Tabular Editor представляет собой интуитивно понятный интерфейс, в котором содержится навигатор по объектам модели данных, таким как таблицы, столбцы, меры, связи и другие элементы. С помощью этого инструмента мы будем создавать Calculation Group (группы вычислений или калькуляционные группы) – многократно используемых наборов мер. Они помогают сократить количество необходимых мер в модели, упрощая ее структуру и обеспечивая более понятное и легкое управление.
Для создания Calculation Group нажмите правой кнопкой мыши на Tables – Create New – Calculation Group. Дайте ей имя – Calculation Group Период:
Внутри этой группы мы создадим два элемента вычислений (Calculation Item) – Текущий и Прошлый месяцы. Для этого нажмите правой кнопкой мыши на созданной Calculation Group Период – Create New – Calculation Item, как показано ниже:
Справа сверху в окне Tabular Editor расположен блок Expression Editor, где вы можете писать и редактировать выражения на языке DAX (Data Analysis Expression), предварительно выделив соответствующий Calculation Item. Для Текущего месяца мы используем SELECTEDMEASURE(). Эта функция возвращает значение текущей меры, которая выбрана, без явного указания ее имени, т.е. она будет возвращать значение любой меры, которую мы будем использовать при построении в отчете. Данная функция является мощным инструментом для создания динамических и адаптивных вычислений:
Для Прошлого месяца в блоке Expression Editor мы прописываем формулу, которая сместит рассматриваемую меру на один месяц назад:
CALCULATE ( SELECTEDMEASURE (), DATEADD ( 'SHD Параметры дат'[Дата], -1, MONTH ) )
Для корректного отображения имени столбца в Power BI Desktop необходимо в созданной нами калькуляционной группе найти столбец Name и переименовать его в Период:
Для сохранения и передачи изменений в Power BI Desktop найдите кнопку Save the changes to the connected database или сочетание клавиш Ctrl + S и сверните окно Tabular Editor.
Power BI Desktop попросит вас обновить модель данных, это необходимо сделать, что созданная нами Calculation Group Период отобразилась:
Созданная нами таблица будет отдельно стоящей, то есть она не должна и не будет иметь связь с другими сущностями модели.
Посмотрим на ее содержание. Таблица будет состоять из двух столбцов – Период и Ordinal, и двух строк – Прошлый месяц и Текущий месяц:
Значение Ordinal определяет, как элементы вычисления будут упорядочены и расположены внутри Calculation Group. В нашем случае у обеих строк Ordinal = -1, это говорит о том, что они будут отображаться и применяться без явного порядка. Но при необходимости вы можете задать в Tabular Editor для Текущего месяца значение 0, а для Прошлого месяца значение 1, или наоборот:
Мы подготовили необходимые расчеты, теперь перейдем к созданию карточки. В поле карточки поместим меру [Заказы: Заработано], которую мы использовали в предыдущих страницах отчета:
Заказы: Заработано = SWITCH ( SELECTEDVALUE ( 'Расчет заказов'[Тип расчета заказа] ), "по дате создания", SUMX ( 'GC Заказы', IF ( RELATED ( 'GC Параметры заказов'[Статус] ) <> "Отменен", 'GC Заказы'[Заработано], BLANK () ) ), "по дате закрытия", CALCULATE ( SUMX ( 'GC Заказы', IF ( RELATED ( 'GC Параметры заказов'[Статус] ) <> "Отменен", 'GC Заказы'[Заработано], BLANK () ) ), USERELATIONSHIP ( 'GC Заказы'[Идентификатор даты закрытия], 'SHD Связь календарей'[Идентификатор даты] ) ), BLANK () )
Напомним, что данная мера будет считать сумму столбца [Заработано] из сущности ‘GC Заказы’, при условии, что [Статус] заказа не принимает значение “Отменен”. Также мера учитывает значение среза “Отображение заказов”. Если пользователь выберет “по дате создания”, то будет идти подсчет заработанной суммы по активной связи между ‘GC Заказы’ и ‘SHD Связь календарей’, а если “по дате закрытия”, то функция USERELATIONSHIP отключит эту связь и включит неактивную связь между этими таблицами по столбцу [Идентификатор даты закрытия].
Для того, чтобы в карточке отображалась заработанная сумма только за текущий месяц откроем панель Фильтров. В блок Фильтры для этого визуального элемента поместим поле [Период] из таблицы ‘Calculation Group Период’ и выберем значение Текущий месяц, как показано ниже:
Аналогичным образом создайте карточку для Прошлого месяца.
Далее необходимо создать меру Отклонение в процентах:
Отклонение Заработано = VAR Otkl = DIVIDE ( CALCULATE ( [Заказы: Заработано], 'Calculation Group Период'[Период] = "Текущий месяц" ), CALCULATE ( [Заказы: Заработано], 'Calculation Group Период'[Период] = "Прошлый месяц" ) ) - 1 RETURN SWITCH ( TRUE (), Otkl > 0, "+" & FORMAT ( otkl, "0%" ), Otkl )
Данная мера делит значение меры [Заказы: Заработано] за текущий период на эту же меру за прошлый период. Чтобы рассчитать относительное изменение нужно из результата деления вычесть единицу, а затем результат отобразить в процентах с помощью функции FORMAT.
Создадим еще одну меру, которая будет менять цвет Отклонения в зависимости от значения, используя HEX Color Codes:
Цвет Отклонение Заработано = SWITCH ( TRUE (), [Отклонение Заработано] < 0, "#BE4A47", //красный "#278E84" ) //зеленый
В Форматировании визуального элемента – Значение выноски, возле Цвета нажмите на значок функции fx. Стиль формата выберите Значение поля и установите меру [Цвет Отклонение Заработано].
На первый взгляд кажется, что это долгий способ, чтобы решить такую простую задачу, и вы будете правы, если бы все ограничилось только созданием одной карточки. В нашем кейсе созданную калькуляционную группу для периодов мы будем использовать в различных визуальных элементах, при этом в значениях мы будем подставлять различные меры, ввиду того, что при создании Calculation Item мы использовали не конкретную меру, а SELECTEDMEASURE(). В противном случае, для каждой визуализации нам пришлось бы писать свою меру. Таким образом, создание Calculation Group с помощью Tabular Editor – это универсальное решение, которое значительно упрощает построение отчетов и избавляет нас от необходимости создавать множество расчетов с одинаковой логикой.
Стоит отметить, что в нашем шаблонном отчете в качестве периода мы выбрали месяц, но при необходимости вы можете доработать отчет в разрезе, необходимом вам (неделю, квартал, год).
Аналогичным способом создаем карточки Количество заказов и Количество пользователей, только в поля необходимо поставить меры [Заказы: Количество] и [Пользователи: Количество] соответственно. Но в панели фильтров для каждого визуального элемента будем использовать ту же калькуляционную группу по периодам.
График
Используя созданную нами калькуляционную группу, мы можем легко построить график динамики по дням. Для этого в Оси Х графика расположим поле [Дата] из ‘SHD Параметры дат’, в Оси Y – меру [Заказы: Заработано], а в Условные обозначения – Период из ‘Calculation Group Период’:
Столбчатая диаграмма
Теперь мы разберем, как построить столбчатую диаграмму с подписями отклонения, используя стандартные средства Power BI Desktop, слегка доработав их в Tabular Editor.
Начнем с того, что добавим на холст График и гистограмма с группировкой и в ось Х добавим Менеджера из ‘GC Параметры заказов’, в ось Y – меру [Заказы: Заработано], а в условные обозначения – ‘Calculation Group Период’ [Период]:
Далее нам надо будем отобразить отклонение в процентах: на сколько выросла или упала заработанная сумма у менеджера в текущем периоде по отношению к предыдущему. И здесь мы будем творить чудо! Для начала создадим две отдельные меры, первая из которых будет отображать значение, если отклонение упало, а вторая – если отклонение выросло или не изменилось. Это мы сделаем для того, чтобы в дальнейшем мы смогли первую меру покрасить в красный цвет, а вторую – в зеленый.
Линия Отклонений Заработано Уменьшение = VAR ThisMonth = CALCULATE ( [Заказы: Заработано], 'Calculation Group Период'[Период] = "Текущий месяц" ) VAR LastMonth = CALCULATE ( [Заказы: Заработано], 'Calculation Group Период'[Период] = "Прошлый месяц" ) RETURN IF ( ISBLANK ( LastMonth ), BLANK (), IF ( ThisMonth < LastMonth, LastMonth * 1.2 ) )
Линия Отклонений Заработано Увеличение = VAR ThisMonth = CALCULATE ( [Заказы: Заработано], 'Calculation Group Период'[Период] = "Текущий месяц" ) VAR LastMonth = CALCULATE ( [Заказы: Заработано], 'Calculation Group Период'[Период] = "Прошлый месяц" ) RETURN IF ( ISBLANK ( LastMonth ), BLANK (), IF ( ThisMonth >= LastMonth, ThisMonth * 1.2 ) )
Разберем эти меры. Мы помним, чтобы рассчитать отклонение, необходимо заработанную сумму за текущий месяц разделить на заработанную сумму за прошлый месяц и вычесть единицу, поэтому создадим две переменные ThisMonth и LastMonth , которые позволяют обратиться к ним в теле формулы. Внешнее условие IF проверяет на пустоту переменную LastMonth, чтобы избавить нас от значения отклонения -100%. Внутренний IF проверяет на увеличение или уменьшение отклонения и умножает заработанную сумму на коэффициент 1,2. Это необходимо для того, чтобы значение отклонений на графике располагались чуть выше столбиков заработанной суммы, т.е. само значение отклонения в данных мерах не рассчитывается, в этом и состоит магия, расчет будет происходить чуть позже.
Созданные меры поместим в Ось Y линии для уже подготовленной комбинированной диаграммы и немного отредактируем ее. В форматировании визуального элемента включим Метки данных для обеих Линий, отключив у столбцов Прошлый месяц и Текущий месяц. Продолжаем настройку меток данных для каждой Линии: Положение (линия) выберем “сверху”, отключим фон и в значениях зададим цвет для уменьшения – красный, а для Линии увеличения – зеленый. Зайдем в Строки – Цвета и для Линий также зададим соответствующие цвета. В поле Ось Y линии двойным нажатием переименуем в Уменьшение и Увеличение, как показано ниже:
Далее создадим меру отклонения для графика, т.е.то самое значение меры, которое мы собирались создать позже. Ранее созданная мера [Отклонение Заработано] для карточки нам не подойдет, ввиду того, что внутри этой меры мы задали пользовательский формат, т.е. превратили в текст, а для данного графика нам нужен формат – числа.
Отклонение Заработано График = VAR Otkl = DIVIDE ( CALCULATE ( [Заказы: Заработано], 'Calculation Group Период'[Период] = "Текущий месяц" ), CALCULATE ( [Заказы: Заработано], 'Calculation Group Период'[Период] = "Прошлый месяц" ) ) - 1 RETURN Otkl
Мы подготовили все расчеты, теперь запускаем Tabular Editor через Внешние инструменты и создаем новый ‘Calculation Group Заработано’ и Calculation Item с именем “Подпись Отклонения Сумма” так, как описано в статье выше. Столбец Name сразу переименуем в [Отклонение Сумма]. У вас должно получится как показано ниже:
Встанем на Calculation Item [Подпись Отклонения Сумма] и в Expression Editor зададим формулу SELECTEDMEASURE().
Мы задали значение для элемента вычисления, теперь необходимо указать формат. Для этого в Property: поменяем с Expression на Format String Expression и для формата создадим формулу:
IF ( ISSELECTEDMEASURE ( [Линия Отклонений Заработано Увеличение] ) || ISSELECTEDMEASURE ( [Линия Отклонений Заработано Уменьшение] ), """" & FORMAT ( [Отклонение Заработано График], "▲ +0%;▼ -0%;-" ) & """", SELECTEDMEASUREFORMATSTRING () )
где описывается, что если выбранная мера [Линия Отклонений Заработано Увеличение] или [Линия Отклонений Заработано Уменьшение], то используем пользовательский формат для значения меры [Отклонение Заработано График], причем задаем отдельный формат для увеличения, уменьшения и нулевого значения, в противном случае оставляем формат меры при помощи функции SELECTEDMEASUREFORMATSTRING().
Треугольники, показывающие направление изменения реализованы с помощью эмодзи, которые вы можете найти в интернете, скопировать и вставить в тело формулы, либо воспользоваться сочетанием клавиш alt num 30 (треугольник вверх) и alt num 31 (треугольник вниз).
Двойные кавычки “”””, окружающие результат форматирования добавляют пустые начало и окончание строки. Это обеспечивает надежность и стабильность выражения, предотвращая появление ошибок.
Сохраним изменения в Tabular Editor и свернем его, чтобы вернуться в Power BI Desktop. Столбец [Отклонение Сумма] из ‘Calculation Group Заработано’ перенесем в панель фильтров в блок Фильтры для этого визуального элемента и поставим галочку возле Подпись Отклонения Сумма, как показано ниже.
Таким образом мы построили комбинированную диаграмму, но сделали ее более информативной, адаптировав под наши запросы.
Аналогичным образом необходимо построить остальные визуализации во всех закладках листа “Сравнение месяцев”.
Данный анализ может служить основой для факторного анализа и поможет посмотреть как менялись показатели в динамике, а также как они соотносятся с другими показателями в своей группе. В целом, анализ сравнения месяцев может предоставить ценные инсайты для принятия более информированных решений и выявления тенденций.
Отчет по продажам интересен тем, что содержит все основные сущности выгрузки данных GetCourse, настроенную модель и расчеты, которые вы можете использовать для своих запросов. А также он интересен с точки зрения реализации, в частности ABC-анализ и Когортный анализ, которые мы рассмотрели в прошлый статьях блога. Приемы и подходы, которые используются для их построения интересны и могут быть вами использованы в собственных проектах. Данный Отчет по продажам, как и многие другие, вы можете найти в mybi market.
