Вы не задумывались, почему в финансовых отчетах данные представлены в том порядке, в каком они там есть? Элементы затрат, например, всегда начинаются с материалов, затем зарплата, аренда, амортизация и т.д. Статьи затрат также идут в своем порядке. Также детализация по городам: частый вариант по удалению от столицы или по численности населения.
Почему порядок именно такой? Удобно ли это для принятия решений? На самом деле – нет. В большинстве случаев данные эффективней располагать по убыванию оцениваемого показателя.
Другая ситуация – вас могут попросить вывести не всех менеджеров, а только десятку лучших. И вам потребуется ранжировать данные, а затем отсортировать по ТОП-10. В Power Pivot и Power BI несколько способов, как это сделать. Один из них – специальная DAX-формула RANKX.
Расчет рейтингов с RANKX
Итак, задача: присвоить рейтинг в зависимости от величины показателя. Например, определить места менеджеров по сумме продаж за период. Для правильной работы RANKX потребуется справочник, для которого выполняется ранжирование. Поэтому чтобы присвоить рейтинги менеджерам, в модели данных должен быть справочник с ФИО. У нас в примере это таблица ‘менеджеры’. Пример вычислений с ранжированием по менеджерам:
Продажи = SUM ( 'Данные'[Выручка] )
Рейтинг = RANKX ( ALL ( 'Менеджеры'[Менеджер] ), [Продажи] )
Добавим рейтинги в таблицу. Всё верно, но в итоговой строке появилась «лишняя» единица.
Поменяем формулу, чтобы в строке с итогами не выводились ненужные цифры. В Power BI для управления итоговой строкой используется формула ISINSCOPE. В Power Pivot в Excel такой формулы нет и там можно воспользоваться функцией ISFILTERED.
Рейтинг =
IF (
ISINSCOPE ( 'Менеджеры'[Менеджер] ),
RANKX ( ALL ( 'Менеджеры'[Менеджер] ), [Продажи] )
)
Получилась таблица без цифр в итоговой строке. Рейтинги можно посмотреть за период или в динамике, например, на Bump chart – диаграмме изменений.
Синтаксис RANKX
Какие еще есть способы расчета рейтингов? Порядок ранжирования можно задать в самой формуле.
RANKX ( <таблица> , <выражение> [, <значение> [, <порядок> [, <ранг после одинаковых> ] ] ] )
таблица — таблица с данными;
выражение — выражение, рассчитывающее значения для ранжирования;
необязательные:
значение — добавляется к возможным значениям выражения, если такого значения нет;
порядок — порядок ранжирования: ASC – по возрастанию, DESC – по убыванию;
ранг после одинаковых — способ определения ранга, если есть одинаковые значения выражения — для одинаковых значений будет определен одинаковый ранг, а следующим присвоится:
Skip – следующее значение ранга + количество предыдущих одинаковых значений,
Dense – следующий по порядку ранг.
Необязательные части формулы можно пропустить, указав подряд несколько запятых: , , ,
- ASC или DESC определяет порядок ранжирования – по возрастанию или по убыванию.
Рейтинг DESC = RANKX ( ALL ( 'Менеджеры'[Менеджер] ), [Продажи], , DESC )
Рейтинг ASC = RANKX ( ALL ( 'Менеджеры'[Менеджер] ), [Продажи], , ASC )
Если в формуле указать ASC, то у наименьшего показателя будет самый высокий рейтинг. Так удобно ранжировать дебиторскую задолженность – чем она меньше, тем лучше. Если выбрать DESC, то ранжирование будет от большего к меньшему.
- Dense и Skip нужны, чтобы определить ранг после появления одинаковых показателей.
В примере у некоторых менеджеров были одинаковые продажи, поэтому им присвоены одинаковые места. То, какое место займет следующий менеджер, будет зависеть от выбора Dense и Skip.
Рейтинг Dense = RANKX ( ALL ( 'Менеджеры'[Менеджер] ), [Продажи] , , , Dense )
Рейтинг Skip = RANKX ( ALL ( 'Менеджеры'[Менеджер] ), [Продажи] , , , Skip )
В случае с Dense для одинаковых значений будет определен одинаковый ранг. Для следующего значения ранг будет считаться последовательно, по порядку.
Если выбрать Skip, то у одинаковых значений тоже будет один ранг. А следующий ранг будет определяться так, чтобы общее число мест было равным количеству менеджеров.
