Мы создаем отчеты в Pbi для контекстной рекламы, и добавляем в них различные функции по запросу маркетолога или клиента. И часто бывает, что функционала базовых визуализаций становится недостаточно.
Например, как уместить все необходимые показатели на один график, чтобы можно было смотреть динамику, например, по расходу и доходу, а также по расходу и количеству покупок, расходу и ROI на одном графике?
Решаем задачу вывода нескольких показателей на график с возможностью выбора любых комбинаций. Вместо двух-трех графиков оставляем всего один, освобождаем место на дашборде. Расширяем функционал базовых визуализаций с помощью DAX.
Возьмем простой дата-сет для примера
Период |
Категория |
Показатель 1 |
Показатель 2 |
1 |
Категория 1 |
100 |
9 |
1 |
Категория 2 |
15 |
2 |
1 |
Категория 3 |
50 |
6 |
2 |
Категория 1 |
105 |
10 |
2 |
Категория 2 |
20 |
4 |
2 |
Категория 3 |
60 |
5 |
3 |
Категория 1 |
95 |
8 |
3 |
Категория 2 |
25 |
3 |
3 |
Категория 3 |
50 |
6 |
4 |
Категория 1 |
100 |
9 |
4 |
Категория 2 |
10 |
2 |
4 |
Категория 3 |
55 |
7 |
5 |
Категория 1 |
110 |
11 |
5 |
Категория 2 |
15 |
3 |
5 |
Категория 3 |
65 |
4 |
На основе этой таблицы делаем простой визуал (страница “Базовый визуал” в отчете)
Рассчитаем “Показатель 3” в качестве меры по формуле:
Показатель 3 = DIVIDE (
sum ('Таблица' [Показатель 2]),
sum ('Таблица' [Показатель 1]),
0 ) Базовое решение
Задача: сделать удобный график с возможностью отображения всех трех показателей.
В этом случае невозможно переоценить комбинацию функций SELECTEDVALUE и SWITCH для расширения базового управления визуализациями. Чтобы создать необходимый для выбора отображаемого показателя слайсер создадим таблицу с помощью функции DATATABLE. Я пользуюсь именно функцией для создания таблицы потому, что в случае добавления дополнительного показателя таблицу можно легко расширить не нарушая связи с формулами и визуализациями. Итак:
Простой слайсер = DATATABLE (
"Выбор показателя", STRING,
{ { "Показатель 1" }, { "Показатель 2" }, { "Показатель 3" } } )В итоге получается таблица вида:
Выбор показателя |
Показатель 1 |
Показатель 2 |
Показатель 3 |
И срез “Выбор показателя”
Теперь воспользуемся функциями SWITCH и SELECTEDVALUE для создания меры, которая будет сменять выводимые на график значения в соответствии с выбором на срезе “Выбор показателя”:
Вывод показателя на график = SWITCH (
SELECTEDVALUE ('Простой слайсер' [Выбор показателя]),
"Показатель 1", sum ('Таблица' [Показатель 1]),
"Показатель 2", sum ('Таблица' [Показатель 2]),
"Показатель 3", 'Таблица' [Показатель 3])Мера возвращает сумму по показателю 1 и показателю 2 или значение меры “Показатель 3” в соответствии со значением SELECTEDVALUE. Добавляем меру на график в качестве единственного значения и получаем возможность менять выводимый показатель.
В качестве приятного дополнения можно сделать динамическое название, заведя специальную меру:
Динамическое название = CONCATENATE (SELECTEDVALUE ('Простой слайсер' [Выбор показателя]), " в динамике")
и визуализировав ее с помощью карточки или многострочной карточки.
Задача с удобным динамичным отображением разных показателей решена, но этот способ имеет ряд ограничений:
Разноформатные значения унифицируются.
В нашем примере “Показатель 3” мера, которая считает долю показателя 2 в показателе 1 - её логично выводить в процентном виде. Но на графике она будет выглядеть как десятичная дробь.
Можно попробовать навязать формат, но применение функции FORMAT в таком виде:
Вывод показателя на график = SWITCH (
SELECTEDVALUE ('Простой слайсер'[Выбор показателя]),
"Показатель 1", FORMAT (sum ('Таблица'[Показатель 1]), "General Number"),
"Показатель 2", FORMAT (sum ('Таблица'[Показатель 2]), "General Number"),
"Показатель 3", FORMAT ('Таблица'[Показатель 3], "Percent"))
не позволяет работать с графиками, однако в карточках и таблицах подобный метод имеет широкое использование.
Выбор нескольких показателей сразу функцией SELECTEDVALUE воспринимается, как “не выбрано ничего”
В таком случае, лучше ограничивать срез “Выбор показателя” единичным выбором.
Наша доработка базового решения
Для решения задачи с мультивыбором потребуется совершенно иная логика. Нам нужен инструмент, который будет проверять выбрано ли среди прочих конкретный показатель и возвращать логическое true или false соответственно.
Как мы уже успели убедиться, функция SELECTEDVALUE не умеет работать с мультивыбором. Основная проблема заключается в том, что значение столбца “Выбор показателя” текстовое, с ним нельзя проводить никаких операций кроме соотнесения с таким же текстовым значением. К тому же мультивыбор показателей подразумевает работу с динамическим массивом значений - тут напрашивается матричная структура:
Отобразить на графике |
Показатель 1 |
Показатель 2 |
Показатель 3 |
Показатель 1 |
1 |
0 |
0 |
Показатель 2 |
0 |
1 |
0 |
Показатель 3 |
0 |
0 |
1 |
Построенный по столбцу “Отобразить на графике” срез будет фильтровать эту таблицу по всем возможным сочетаниям выбора показателей, а, например, в сумме по столбцу “Показатель 1” вернет единицу только в случае выбора значения “Показатель 1” в слайсере.
Такую таблицу, как и в первом примере, лучше заводить с помощью функции DATATABLE:
Сложный слайсер = DATATABLE (
"Выбор показателя", STRING,
"Показатель 1", INTEGER,
"Показатель 2", INTEGER,
"Показатель 3", INTEGER,
{
{ "Показатель 1" ,1,0,0},
{ "Показатель 2" ,0,1,0},
{ "Показатель 3" ,0,0,1}
})Теперь можно использовать сумму по каждому столбцу как логическое условие отображения того или иного показателя:
Показатель 1 на графике = if (
sum ('Сложный слайсер' [Показатель 1]) = 1,
sum ('Таблица' [Показатель 1]),
"")Показатель 2 на графике = if (
sum ('Сложный слайсер' [Показатель 2]) = 1,
sum ('Таблица' [Показатель 2]),
"")
Показатель 3 на графике = if (
sum ('Сложный слайсер' [Показатель 3]) = 1,
'Таблица' [Показатель 3],
"")Важно в случае, если логическое выражение принимает значение false, возвращать именно “” - пустое значение, а не 0. Иначе, если какой-то показатель не будет выбран, то на графике отобраться нулевые значения.
Выводим на график все три новые меры.
Заметим, что для меры “Показатель 3 на графике” можно присвоить процентный формат. Однако, если выводить его с другими показателями ось унифицирует значения.
Это можно исправить, если включить отображение вспомогательной оси и добавить “Показатель 3” в качестве вспомогательного значения. Теперь динамику числового и процентного показателей можно изучить на одном графике.
Однако осталась еще одна проблема - если снять выбор с фильтра, то отобразятся все три графика.
Сложный слайсер = DATATABLE (
"Выбор показателя", STRING,
"Показатель 1", INTEGER,
"Показатель 2", INTEGER,
"Показатель 3", INTEGER,
{
{ "Показатель 1" ,1,0,0},
{ "Показатель 2" ,0,1,0},
{ "Показатель 3" ,0,0,1},
{ "Проверка" ,1,1,1}
})Теперь, если не выбран ни один параметр на срезе, сумма по любому столбцу будет равна 2, и логический тест, например, “sum ('Сложный слайсер' [Показатель 1]) = 1” вернет false
А для того, чтобы строка “Проверка” не мешала в слайсере, можно убрать её фильтром на уровне визуального элемента
В завершении добавим, что далеко не все показатели сочетаются на одном графике, большое количество добавленных показателей может испортить визуализацию.