Пользовательская сортировка каскадной диаграммы (Waterfall) в Power BI

Недавно в чате о Power BI в Telegram был задан вопрос – возможно ли применить для графика Waterfall (каскадная диаграмма, она же «Водопад») нестандартную динамическую сортировку: положительные значения показываются по убыванию, а отрицательные наоборот, по возрастанию (то есть, сначала самые большие по модулю отрицательные значения, затем минуса поменьше и самые мелкие – в конце).

В итоге из такого графика:

Нужно получить вот такой:

Стандартными средствами мы можем сортировать Waterfall только по возрастанию или убыванию, по обычным правилам (настроить и проверить сортировку можно нажав на три точки в правом верхнем углу визуала):

Нам же нужно сделать так, чтобы:

1. Можно было применить нестандартную сортировку;
2. Она должна быть динамической, то есть реагировать на фильтры (например, по дате)

Решение этой задачи делится на две подзадачи:

1. Найти способ применить пользовательскую сортировку
2. Определить и реализовать алгоритм сортировки

Можем ли мы применить пользовательскую сортировку?

Первая подзадача в случае визуального элемента Waterfall (каскадная диаграмма) решается просто: мы можем использовать для сортировки любые поля, помещенные в одну из областей визуального элемента.

У Waterfall таких областей четыре:

1. Категория
2. Распределение
3. Ось Y
4. Подсказки

Первые две используются для определения значений оси X, третья – для определения размера столбиков, а вот четвертая используется для вывода информации во всплывающем окошке при наведении мыши на элемент графика.

Вот ее мы и будем использовать для того, чтобы отсортировать значения на графике в нужном нам порядке.

Скажу сразу – если бы мы не могли использовать эту область для сортировки, то и решение было бы кардинально другим, если бы вообще было.

Как задать правило сортировки?

Вспомним, что сортировка должна учитывать возможность применения пользовательских фильтров, поэтому нам не подойдет вычисляемый столбец в DAX или Power Query). То есть, нам нужна мера.

Теперь нам надо придумать, как создать такую формулу, которая будет давать нам нужный порядок сортировки.

Вспоминаем вводные условия:

1. Если значение положительное, то мы должны использовать обычный порядок – например, от большего к меньшему
2. Если значение отрицательное, то порядок должен быть обратным – от меньшего к большему.

Самым распространенным способом создания динамической сортировки является использование функции RANKX в формуле меры.

Давайте посмотрим на исходные данные, отсортированные в обычном порядке:

Мера Simple Rank, использованная в таблице – это обычный RANKX, рассчитанный на основе меры [Amount] (в данном случае не важно, что именно считает эта мера, главное, что нам нужно сортировать ее значения).

Нам надо как-то переписать эту меру, чтобы возвращаемый ранг учитывал пожелания.

Так как в задаче было описано условие, что отрицательные числа должны сортироваться по убыванию модуля, то первым порывом был расчёт модуля значений меры [Amount] при помощи функции ABS. Однако, если брать в расчёт не только отрицательные, но и положительные значения (а у нас именно такой случай), их модули перемешиваются, и мы получим кашу из + и – значений.

Еще одним вариантом решения было использование функции IF внутри функции RANKX. Но, перепробовав несколько вариантов, я окончательно запутался в контекстах вычисления, оставил эту затею и вернулся к переосмыслению задачи.

Давайте посмотрим на ряд чисел, отсортированный по убыванию:

+4, +2, -2, 4, -8, -16

Если мы присвоим им ранг по возрастанию значения (самое маленькое число имеет самый маленький по номиналу ранг), то мы получим вот такой ряд рангов:

6, 5, 4, 3, 2, 1

Нам надо как-то сделать, чтобы самое маленькое отрицательное число имело самый большой ранг среди отрицательных чисел.

В итоге после некоторой медитации на табличку с рангами и значениями решение пришло само. Как всегда, оно лежало на виду, надежно прикрытое привычкой считать, что ранг – это ряд положительных чисел, который начинается, например, с 1.

На самом деле ранг может быть представлен любым числом, в том числе отрицательным. Отрицательный ранг будет всегда меньше даже самого маленького положительного ранга. Значит, если ранг отрицательного числа сам будет отрицательным, то отрицательные числа точно будут идти после положительных.

Половину проблемы мы решили. Осталось только «перевернуть» ранг отрицательных. Но это сделать уже очень просто. В DAX, как и в Excel, есть непримечательная функция SIGN (в русскоязычных версиях Excel она называется ЗНАК), которая возвращает 1, -1 или 0 в зависимости от знака числа, переданного ей в аргументе.

Если мы возьмем результат этой функции от ранжируемого значения и умножим его на посчитанный ранг, то для положительных чисел ранг останется неизменным (мы же просто умножаем его на 1). А вот для отрицательных значений ранг поменяет свой знак.

Что это значит с точки зрения математики?

Изначально самое большое отрицательное число (-2) имеет самый большой ранг (4). Самое маленькое отрицательное число (-16) имеет самый маленький ранг (1)

После умножения ранга на сигнум самый большой ранг отрицательного числа (4) стал самым маленьким среди рангов отрицательных чисел (-4), а самый маленький ранг отрицательного числа (1) стал самым большим рангом среди рангов отрицательных числа (-1).

То есть, порядок ранга отрицательного числа «перевернулся», что мы и хотели получить.

Осталось только подправить нашу меру ранга:

И поместить эту меру в подсказки каскадной диаграммы,

настроив соответствующую сортировку:

Пользуйтесь! Файл с примером здесь 😊