Россия Нет

RANKX — функция ранжирования (ранга) в DAX (Power BI и Power Pivot)

 Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):


Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В этой статье мы рассмотрим DAX функцию RANKX, создающую ранги среди значений (ранжирование) в Power BI или Power Pivot.

Разберем параметры этой функции, свойства и примеры формул.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

 

DAX функция RANKX в Power BI и Power Pivot

RANKX () — возвращает ранг выражения, вычисляемого в рамках текущего контекста исходного списка значений для каждой строки текущей таблицы.

Синтаксис:

RANKX ('Таблица'; Выражение; Значение; Порядок; Равные Значения)

Где:

  • Таблица — исходная таблица или табличное выражение
  • Выражение — любое выражение, вычисляемое для каждой строки исходной таблицы с целью формирования полного списка возможных значений для ранжирования
  • Значение — (необязательный параметр) любое выражение, ранг которого необходимо найти. По умолчанию, если этот параметр не указан, вместо этого параметра возвращается значение выражения из текущей строки
  • Порядок — (необязательный параметр) вид ранжирования параметра «Значение»:
    1) DESC — упорядоченный порядок рангов по убыванию (по умолчанию);
    2) ASC — по возрастанию
  • Равные Значения — (необязательный параметр) способ определения ранга при наличии равных значений:

    1) Skip — (по умолчанию) ранг значения, идущего после ряда одинаковых значений, определяется как ранг равных значений плюс количество этих равных значений. Например, имеется 3 равных значения с рангом 5, следующее значение будет иметь ранг 8 (5+3);
    2) Dense — ранг значения, идущего после ряда одинаковых значений, определяется как следующий ранг. Например, имеется 3 равных значения с рангом 5, следующее значение будет иметь ранг 6

Пример формулы на основе DAX функции RANKX

В Power BI Desktop имеется исходная таблица «Продажи Менеджеров»:

Требуется создать меру ранжирования менеджеров, исходя из сумм их продаж. Иначе говоря, нужно создать ранг по продажам, где, ранг 1 соответствует самой большой сумме продажи, а ранг 2 и далее, меньшим суммам продаж.

Воспользуемся рассматриваемой DAX функцией RANKX и попробуем на основе нее составить код формулы для меры [Ранг]:

Ранг = 
RANKX (
    'ПродажиМенеджеров';
    SUM ('ПродажиМенеджеров'[Продажи])
)

В данной формуле, согласно синтаксису функции RANKX, мы использовали только 2 обязательных параметра, остальные (необязательные) мы не стали прописывать.

В качестве первого параметра мы прописали ссылку на исходную таблицу «Продажи Менеджеров».

Во втором параметре указали нужное нам выражение, которое должно рассчитываться для каждой строки исходной таблицы, указанной в первом параметре. А именно, произвели суммирование значений столбца [Продажи] при помощи функции SUM (функция суммирования в языке DAX).

Посмотрим, как отработала, созданная нами мера расчета ранга, в отчетах Power BI:

Как мы видим из визуализации в Power BI, наша формула на основе функции RANKX отработала неверно и возвратила для каждого менеджера один и тот же ранг, равный 1.

Причины этой ошибки 2:

  1. В этой визуализации при расчете конкретной ячейки ранга, значение столбца [Менеджер] является фильтром исходной таблицы.Например, рассмотрим строку с менеджером Петров — при расчете ранга Петрова, исходная таблица будет автоматически отфильтрована только этим менеджером и сумма продаж возвратится только по одному Петрову.Соответственно, ранг рассчитается только исходя из одного этого менеджера и, естественно, ранг будет равен 1. И так по каждому менеджеру.Решить эту проблему мы сможем при помощи еще одной функции языка DAX — ALLSELECTED (прочитать информацию об этой функции Вы можете в этой статье), обернув в эту функцию исходную таблицу из первого параметра. Этим самым, мы удалим все фильтры, которые наложились на столбец [Менеджер] строками самой визуализации.
  2. Так как выражение во втором параметре вычисляется по каждой строке исходной таблицы, указанной в первом параметре, то необходимо передать контекст этой строки из первого параметра во второй параметр.Но, проблема в том, что функция SUM не принимает контекст строки.Чтобы решить эту вторую проблему, нужно обернуть функцию SUM в другую DAX функцию — CALCULATE, которая сможет передать контекст строки из таблицы в выражение. И тогда функция SUM рассчитает сумму продаж в рамках этого контекста строки, то есть, рассчитает сумму продаж только по одному конкретному менеджеру.

Итак, исправим нашу формулу согласно этим двум пунктам:

Ранг = 
RANKX (
    ALLSELECTED ('ПродажиМенеджеров');
    CALCULATE ( SUM ('ПродажиМенеджеров'[Продажи]))
)

Испробуем эту доработанную меру в Power BI:

Теперь, наша мера расчета ранга по продажам менеджеров работает как надо. У менеджера Воснецовой сумма продаж составляет 530745, что является самой наивысшей суммой и поэтому у этой суммы продаж ранг 1. А у менеджера Петров самая минимальная сумма продаж, равная 120000 и именно поэтому, у его продаж ранг 4.

Если же мы хотим поменять порядок ранжирования, чтобы у самой минимальной суммы был ранг 1, а у самой максимальной суммы — ранг 4, то в параметрах функции RANKX нужно добавить 4 параметр «Порядок» и поставить у него значение ASC (по возрастанию). Тогда формула будет такой:

Ранг = 
RANKX (
    ALLSELECTED ('ПродажиМенеджеров');
    CALCULATE ( SUM ('ПродажиМенеджеров'[Продажи]));
    ;
    ASC
)

Исходя из синтаксиса функции RANKX в этой формуле в параметрах мы указали 3 параметра — первый, второй и четвертый. Третий и пятый мы пропустили. Этим самым и объясняется лишняя точка с запятой перед ASC в новой измененной формуле, то есть, это и есть пропущенный необязательный третий параметр.

Итак, посмотрим на результат в Power BI:

Теперь, как мы видим, ранг 1 соответствует самой минимальной сумме продаж, а ранг 4 — самой максимальной.

На этом, с разбором DAX функции RANKX, создающей ранги (ранжирование) в Power BI или Power Pivot, все.

Пожалуйста, оцените статью:

  1. 5
  2. 4
  3. 3
  4. 2
  5. 1
(46 голосов, в среднем: 4.7 из 5 баллов)
[Экспресс-видеокурс] Быстрый старт в языке DAX

 
 
Антон БудуевУспехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
 
 
 
 

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
 
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.

 
Понравился материал статьи?
Избранные закладкиДобавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D

Автор статьи:
Категория: Язык функций и формул DAX для Power BI и Excel (Power Pivot)

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

* Заполняя форму отправки комментария своими персональными данными (имя, email, сайт и др.), Вы автоматически подтверждаете свое согласие на обработку своих персональных данных


  1. Ирина:

    Спасибо за ваш сайт и подробно изложенный материал. DAX новичку не так просто понять, как хотелось бы. Но у вас все по полочкам разложено. Благодарю!

  2. Наталья:

    Добрый день.
    Необходимо вывести место в рейтинге Филиала.
    Использую формулу:
    Рейтинг филиалов:=RANKX(ALL(‘Филиал'[наименование]);[отклонение];;ASC;Dense)
    (где, [отклонение] — мера)
    Результат корректный, по всем Филиалам в группе.
    Когда выбираю один из Филиалов результат по части из них меняется.
    Например, 12 место становится 13 и т.п.
    Заранее спасибо.

    1. Антон Будуев:

      Здравствуйте, Наталья.

      К сожалению, мне не совсем ясна исходная информация, но, возможно, проблема из-за функции ALL, попробуйте ее заменить на ALLSELECTED

  3. Yul:

    Огромное спасибо! Русскоязычное сообщество такого уровня (по довольно редкой теме BI) — просто находка.
    Теперь я надолго с вами ))

    1. Антон Будуев:

      пожалуйста))

  4. Алексей:

    Спасибо за сайт.. очень-очень полезного начинающим.. в частности мне точно..

    1. Антон Будуев:

      Пожалуйста, Алексей. Очень рад, что информация блога для Вас полезна

  5. Света:

    Здравствуйте, я писала этот вопрос под видео на ютубе о фильтрах, но мне как раз нужно ранжирование с применением фильтра отсева. Например, есть список менеджеров которых надо отранжировать, но за исключением одного или двух менеджеров, Подскажите, плз, как это прописать?

    1. Антон Будуев:

      Светлана, здравствуйте.

      Очень просто, Вам в условии фильтра нужно прописать «Неравно» и DAX оператор или (||), то есть, например, так: [Менеджер] <> «Петров» || [Менеджер] <> «Сидоров».
      Этим примером мы прописали условие «Столбец Менеджер не равен значениям Петров или Сидоров.
      Данные фильтры прописываете в функции FILTER, а ее уже вставляете вместо таблицы в RANKX. Тогда ранги посчитаются за исключением отфильтрованных Вами значений

  6. Егор:

    Добрый день.

    А нет примера когда данные находятся в разных таблицах, например в одной таблице матрица (Товар, Поставщик, Группа и тд) и таблица фактов (продажи, связь с матрицей через Товар). Как например посчитать ранги только по поставщикам?

    1. Антон Будуев:

      Если ранг нужно посчитать по 2 связанным таблицам, то в первом параметре RANKX указываем столбец из связанной таблицы-справочника (по чему нужен ранг). А во втором параметре агрегацию из таблицы фактов:
      РангПоставщиков =
      RANKX (
      ALLSELECTED ('спрТовар'[Поставщик]);
      CALCULATE ( SUM ('Продажи'[Продажи]))
      )

      пример RANKX по двум связанным таблицам