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

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

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

Обычная сортировка каскадной диаграммы

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

Нестандартная сортировка

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

Настройка сортировки в каскадной диаграмме Power BI

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Исходные данные для сортировки

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

Simple Rank =
IF (
    ISINSCOPE ( ‘Margin Details'[SubArticle] );
    RANKX ( ALLSELECTED ( ‘Margin Details'[SubArticle] ); [Amount]; [Amount]; ASC )
)

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

Так как в задаче было описано условие, что отрицательные числа должны сортироваться по убыванию модуля, то первым порывом был расчёт модуля значений меры [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). А вот для отрицательных значений ранг поменяет свой знак.

Манипуляции с рангом при помощи функции SIGN

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

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

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

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

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

CustomOrder =
IF (
    ISINSCOPE ( ‘Margin Details'[SubArticle] );
    SIGN ( [Amount] )
        RANKX ( ALLSELECTED ( ‘Margin Details'[SubArticle] ); [Amount]; [Amount]; ASC )
)

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

Помещаем меру ранжирования в область подсказок (tooltips)

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

Сортировка каскадной диаграммы по полю из «Подсказок»

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

Share this
Связи многие-ко-многим в Power BI

Добавить комментарий

Your email address will not be published / Required fields are marked *

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.