Каскадная диаграмма может быть эффективным инструментом для передачи информации.
Но у неё есть некоторые ограничения.
Введение
Для начала давайте определим масштаб задачи. Представьте, что мы хотим отслеживать рост вашей клиентской базы с течением времени.
Мы подсчитываем количество клиентов на основе их первого заказа и определяем новых клиентов за каждый месяц.
Но теперь мы хотим создать визуализацию этого показателя с помощью диаграммы «водопад» и получаем что-то вроде этого:
Но нам нужно нечто большее.
Нам нужно знать начальное значение — количество клиентов на конец предыдущего года, а затем прибавлять количество новых клиентов за каждый месяц к общему количеству клиентов на конец отчётного периода.
Что-то вроде этого:
К сожалению, мы не можем установить один столбец в качестве начального итога в Power BI.
Таким образом, при добавлении показателя в диаграмму «водопад» в Power BI результат будет аналогичен первому примеру в Excel:
Даже при использовании коммерческого визуального инструмента, такого как Zebra BI, результат может оказаться не таким, как ожидалось:
В этом случае я добавил показатель за предыдущий год, чтобы задать начальное значение. Однако визуализация вычисляет отклонение от показателя за предыдущий год для каждого месяца и отображает итоговый показатель на основе этих отклонений.
Результат 141 за предыдущий год рассчитан таким образом, потому что Zebra BI определяет, что за текущий год есть данные только за 7 месяцев, и вычисляет сумму за предыдущий год только за эти 7 месяцев.
В данном конкретном случае это не то, что нам нужно, поскольку нас интересует не отклонение от показателей предыдущего года, а рост, начавшийся в конце прошлого года.
Несмотря на то, что этот пользовательский визуальный эффект чрезвычайно эффективен, в нашем случае он бесполезен.
Расширьте модель данных
Теперь нам нужен способ не только для расчёта, но и для отображения последнего месяца предыдущего года при выборе, например, 2025 года.
Я уже решил эту проблему, добавив вторую таблицу с датами и написав соответствующую формулу.
Полное описание этого решения и объяснение принципа его работы вы можете найти в разделе «Ссылки» ниже.
Модифицированная модель данных выглядит следующим образом:
Эта новая таблица дат позволяет нам работать с элементом, выбранным в таблице «Дата выбора», и выполнять вычисления с помощью таблицы дат.
Чтобы это исправить, я присвоил обеим таблицам статус «Таблица дат». Я по-прежнему могу использовать таблицу «Дата» без ограничений.
Разработка показателя (показателей)
Следующий шаг — написание мер.
Сначала я должен рассчитать начальное значение — количество клиентов на конец (предыдущего) года.
Чтобы достичь этой цели, я должен подсчитать сумму всех строк за предыдущий год, но за все месяцы:
New Customers (YE) =
VAR SelYearPY = SELECTEDVALUE('Date'[Year])
VAR Result = CALCULATE([New Customers]
,REMOVEFILTERS('Date')
,'Date'[Year] = SelYearPY
)
RETURN
ResultВ 2024 году результат составит 280.
Вы можете задаться вопросом, почему я вычисляю сумму за выбранный год, а не за предыдущий.
Дело в том, что мы хотим показать результат этого показателя за декабрь 2024 года (при выборе 2025 года). Пожалуйста, подождите немного, пока не увидите результаты. Они помогут вам разобраться.
Теперь нам нужно разработать функцию, которая будет возвращать правильные значения в зависимости от месяца по оси X.
Это должно быть значение на конец предыдущего года для декабря 2024 года или количество новых клиентов за каждый месяц текущего (выбранного) года. См. предыдущую меру.
Необходимая нам мера должна включать в себя следующие шаги:
- Получите выбранный год из таблицы дат.
- Рассчитайте первый и последний месяцы, которые будут отображаться в отчёте.
- Рассчитайте результат, убрав фильтр из таблицы дат, используемой в срезе (таблица «Дата выбора»).
- Решите, какой результат должен отображаться за каждый месяц.
Последний шаг имеет решающее значение.
Поскольку мы удаляем фильтр для выбранного года из обеих таблиц дат, нам нужно контролировать, должен ли отображаться результат для каждого месяца. Этот шаг вы найдёте в части SWITCH() меры, приведённой ниже.
Это полная мера:
Retail Sales Waterfall =
VAR SelYear = SELECTEDVALUE('Selection Date'[Year])
VAR MinYearMonth = SelYear * 100 + 1
VAR MaxYearMonth = SelYear * 100 + 12
VAR LastPYYearMonth = (SelYear - 1) * 100 + 12
VAR ActualMK = CALCULATE(
MAX('Date'[MonthKey])
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None)
)
RETURN
SWITCH(TRUE()
,ActualMK = LastPYYearMonth
,CALCULATE([New Customers (YE)]
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None)
,REMOVEFILTERS('Date')
,'Date'[MonthKey] = (SelYear - 1) * 100 + 12
)
,ActualMK >= MinYearMonth
&& ActualMK <= MaxYearMonth
,CALCULATE([New Customers]
,CROSSFILTER('Selection Date'[DateKey]
,'Date'[DateKey]
,None)
)
,BLANK()
)Это и есть результат:
Над диаграммой «водопад» вы можете увидеть значения, возвращаемые функцией Measure.
Как упоминалось выше, в верхнем левом углу для выбора года используется столбец «Год» не из таблицы «Дата», а из таблицы «Дата выбора». Это важно. Решение не будет работать, если использовать столбец «Год» из таблицы «Дата».
Заключение
Визуализация в виде водопада отлично подходит для отображения изменения значений от одного элемента к другому. Элементом может быть месяц или что-то другое. Например, в разделе «Ссылки» ниже вы можете найти другую статью, в которой я показываю, как изменить модель данных, чтобы отобразить бизнес-процесс в виде водопада в Power BI.
Представленное здесь решение — типичный пример того, как я повторно использовал ранее разработанный подход для решения новой задачи.
Прелесть этого подхода в том, что он позволяет мне без ограничений использовать все показатели из «обычной» таблицы дат и применять таблицу «Дата выбора» для расширения функциональности отчёта.
В этом случае я добавил в каскадную диаграмму функцию, которая ранее была недоступна.
Я не стану утверждать, что вам всегда нужны две таблицы с датами. Добавляйте их только при необходимости. Нет смысла добавлять таблицы в модель данных только ради того, чтобы они там были.