Вычисление рангов на DAX
Под ранжированием, в общем случае, понимается определение порядкового места какого-либо числа в отсортированном списке других числовых значений. Например, позиция клиента по объёму его покупок относительно всех остальных клиентов, позиция товара по его цене относительно всех товаров и т. п.
В Microsoft Excel подобная задача легко решается с помощью стандартной функции РАНГ (RANK), первый аргумент которой - это число, для которого мы определяем ранг, второй - весь список значений, а третий определяет сортировку (0 - наивысший ранг получает наибольшее значение, 1 - наивысший ранг получает наименьшее значение):
Однако, вычисление рангов с помощью мер на языке DAX в Power Pivot и Power BI уже не так очевидно. Давайте разберемся в этом вопросе.
Рабочая модель
В качестве примера возьмём простую модель данных, состоящую из трёх таблиц:
Здесь:
- Sales - таблица проданных заказов с полями дата продажи, код менеджера, стоимость и т.д. Если заказ вернули, то в поле Возврат будет 1.
- Dates - таблица дат со стандартными полями (дата, год, месяц, квартал и т.д.). Связана с таблицей продаж Sales по полю даты.
- Managers - справочник по менеджерам (код менеджера, имя, дата рождения, пол и т.д.) Связана с таблицей продаж Sales по коду менеджера.
В таблице Sales заранее сделаны три базовые меры для подсчёта количества заказов (как количество строк в Sales), количества возвратов (как суммы единичек по столбцу Возврат) и доли возвратов (как отношение одного к другому с округлением до целых процентов):
Число заказов =
COUNTROWS ( Sales )
Число возвратов =
SUM ( Sales[Возврат] )
Доля возвратов =
ROUND (
DIVIDE (
[Число возвратов],
[Число заказов]
),
2
)
Если построить по имеющимся данных сводную (в Power Pivot) или положить их в Матрицу или Таблицу (в Power BI), то общая картина по количеству заказов по каждому менеджеру будет выглядеть так:
Вычисляем ранг
Предположим, что нам с помощью меры на DAX необходимо вычислить ранг для каждого менеджера по числу проданных им заказов. Например, для Васильева с его наибольшими продажами, эта мера должна выдавать 1, для Новиковой - 2 и т.д. по убыванию до последнего 12-го места у Соколовой.
В языке DAX существует несколько функций ранжирования, но основной является функция RANKX. Как легко сообразить по букве «X» на конце, она относится к категории итераторов (как SUMX, AVERAGEX, MINX и др.) и проходит построчно по заданной таблице, определяя для каждой строки ранг определённого выражения или формулы. Синтаксис этой функции следующий:
= RANKX ( <Table>, <Expression>, <Value>, <Order>, <Ties> )
Первые два аргумента являются обязательными, последние три — необязательными.
- Table — таблица, в которой мы проходим по строкам.
- Expression — выражение или формула, вычисляемая в каждой строке таблицы Table, и для которой оценивается её ранг (позиция) в этой таблице.
- Value — необязательный аргумент, задающий внешнее значение для оценки, если оценивается не предыдущий аргумент Expression. Используется для ранжирования по внешней таблице.
- Order — направление сортировки, используемое при вычислении ранга: DESC (по убыванию) или ASC (по возрастанию). Если не задано, то подразумевается DESC.
- Ties — аргумент, определяющий логику расчёта ранга в случае, когда в таблице есть несколько одинаковых значений: Skip (по умолчанию) — ранг прерывается, Dense — ранг не прерывается.
В самом простом варианте для вычисления ранга каждого менеджера по количеству его заказов нам потребуется создать следующую меру:
Ранг по заказам =
RANKX (
ALL ( Managers ),
[Число заказов]
)
В первом аргументе функции RANKX мы должны задать таблицу, по которой мы проходим и для каждой строки которой мы определяем её ранг относительно всех остальных значений. В нашем случае — это таблица Managers. Однако, если задать её напрямую (не заворачивая в ALL), то мы получим странный результат — по всем людям ранг будет равен 1
Чтобы понять, почему так происходит, необходимо вспомнить, что любая мера считается в контексте фильтров отчёта, т. е., например, в первой строке таблицы мера «видит» только сделки Александра Иванова, во второй — только сделки Анны Новиковой и т. д. Получается, что мы оцениваем ранг каждого сотрудника только относительно него же самого, а не всех менеджеров, как это должно быть. Для корректного вычисления нам необходимо получить полный список всех сотрудников из таблицы Managers, игнорируя текущие фильтры отчёта, что мы и делаем, применяя функцию ALL в первом аргументе.
Со вторым же аргументом функции RANKX всё просто — эта та мера (или формула, если вы заранее не сделали аналогичную меру), которая рассчитывается в каждой строке нашей таблицы из первого аргумента.
Поместив созданную меру в таблицу, мы увидим необходимый нам ранг по каждому менеджеру:
Скрываем итоги
Обратите внимание на значение нашей меры в итоговой строке Всего. Оно всегда будет равно 1, т. к. итог, технически, является самым большим значением в нашей таблице. Формально это правильно, но практического смысла не имеет и может вызвать лишние вопросы у будущих пользователей вашего отчёта. Так что лучше скрыть итоговое значение, используя функции IF и ISINSCOPE:
Ранг по заказам =
IF (
HASONEVALUE ( Managers[Имя менеджера] ),
RANKX (
ALL ( Managers ),
[Число заказов]
)
)
В результате ранг будет вычисляться только по тем строкам, где в вычислениях участвует только один менеджер, а по строке итога (где менеджеров много), будет пусто, как мы и хотели:
Ранг с учётом внешних фильтров
Если помимо таблицы с рангами на текущей странице вашего отчёта присутствуют источники внешних фильтров (срезы, другие влияющие визуализации, фильтры на панели Фильтры (Filters) и т. д.), то это может привести к возникновению ещё нескольких нюансов.
В качестве иллюстрации давайте добавим на страницу с предыдущей ранговой таблицей срез по полу менеджера из Managers[Пол] и выберем на нём, например, мужчин:
Обратите внимание, что в нумерации рангов теперь есть разрывы — после 1-го ранга идёт сразу 3-й, а после него сразу 5-й. Так происходит, потому что функция RANKX изначально выдаёт просто номер строки для текущего значения в общей таблице по всем менеджерам, т.е. пропускает ранги, присвоенные женщинам. Чтобы не учитывать, в данном случае, женщин вообще и брать в расчёт только мужчин, выбранных на срезе, необходимо чуть подкорректировать нашу формулу - использовать в ней вместо ALL функцию ALLSELECTED. Она сначала снимает все фильтры с таблицы Managers, но потом возвращает те фильтры, которые приходят в текущую таблицу из внешнего мира (т.е. из среза по полу в данном случае). Таким образом в таблице Managers остаются все продавцы-мужчины и ранг затем считается именно по ним.
Обратный ранг и пропуски в нумерации
Предположим, что нам необходимо посчитать ранг для наших менеджеров по их доле возвращённых заказов. Учитывая всё вышесказанное, это легко реализовать следующей мерой:
Тут хорошо видно два скользких момента:
- Наилучший ранг (1) получил Илья Павлов, а последнее место (12) - Александр Иванов, хотя по логике должно быть наоборот. Это легко исправить, используя четвёртый необязательный аргумент функции RANKX, отвечающий за сортировку при расстановке рангов. В данном случае, он должен быть равен ASC - по убыванию.
- Менеджеры, у которых была одинаковая доля возвратов (например Новикова, Волков, Васильев, Фёдоров, Кузнецов с 6%) получают одинаковый ранг (6), но это приводит к тому, что следующий менеджер получит не 7-й ранг по порядку, а сразу 11. Чтобы ранги распределялись по порядку без подобных пропусков, нам потребуется 5-й необязательный аргумент в значении Dense (а не Skip по умолчанию).
Таким образом, итоговая подправленная формула будет выглядеть так:
Ранг по внешней таблице
В завершении, для полноты картины, давайте разберём последний аргумент функции RANKX, который мы ещё не использовали — это третий по счёту аргумент Value. Он применяется только в тех случаях, когда мы хотим оценить ранг вычисляемого выражения не по той же таблице, которую указали в первом аргументе Table, а по какой-то другой, внешней. Такое бывает нечасто, но иногда всё же требуется.
Предположим, у нас в компании существует своя система оценки менеджеров по количеству обработанных ими заказов:
- если менеджер обработал 500 или более заказов, то он получает наивысший ранг 1;
- если менеджер обработал от 300 (включительно) до 500 заказов, то он относится к следующему рангу 2;
- если число заказов от 100 (включительно) до 300 — получает ранг 3 и т. д.
Конечно, можно реализовать присвоение таких рангов с помощью логических функций — например, создав меру из нескольких вложенных друг в друга функций IF или одной функции SWITCH:
Ранг с помощью SWITCH =
SWITCH (
TRUE,
[Заказов всего] >= 500, 1,
[Заказов всего] >= 300, 2,
[Заказов всего] >= 100, 3,
[Заказов всего] >= 50, 4,
5
)
Но такой подход, как вы догадываетесь, не отличается особой гибкостью — при изменении пороговых значений рангов или добавлении/удалении рангов в будущем нам придётся вручную корректировать эту формулу. Гораздо изящнее будет добавить в нашу модель несвязанную с другими таблицу Intervals, где зафиксированы пороги по количеству заказов для каждого ранга:
Для расчёта рангов по этой логике нужная нам мера будет как раз использовать третий аргумент RANKX и выглядеть следующим образом:
В будущем созданную таблицу рангов легко можно изменить, добавив туда новые ранги-строки или подкорректировав пороги — наша мера с RANKX после этого пересчитается автоматически.
Ссылки по теме
