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

• Подробное ВИДЕО «Расчет ТОП-3 относительно каждой категории внутри единой таблицы в Power BI и Power Pivot»
• Создание нового столбца с рангами
• Применение фильтра к таблице
• Алгоритм работы DAX функции FILTER в режиме динамической фильтрации
• Расчет ТОП-3 по категориям (месяцам) используя дополнительный столбец
• Расчет ТОП-3 по категориям (месяцам) используя виртуальный столбец
• Алгоритм работы DAX функции ADDCOLUMNS

---

Приветствую Вас, дорогие друзья, с Вами Будуев Антон.

В данной статье мы с вами будем на практике разбирать достаточно насущный вопрос для многих пользователей DAX: как в Power BI и Power Pivot в рамках одной таблицы произвести расчет ТОП-3 индивидуально для каждой категории?

В разбираемом примере нам понадобятся следующие DAX функции: RANKX, FILTER, EARLIER, ADDCOLUMNS, а также оператор IN {}.

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

Совет: скачайте себе «Справочник DAX функций для Power BI и Power Pivot» в PDF формате, чтобы подробное описание DAX функций и примеров формул у Вас всегда было под рукой. А также, скачайте файлы к данной статье (для Power BI и Power Pivot).

 

Подробное ВИДЕО «Вычисляем на языке DAX в Power BI и Power Pivot дату первой продажи товара

Ссылки из видео:
1) [Скачивайте] Файлы к уроку для Power BI и Power Pivot: скачать
2) [Скачивайте PDF] Справочник DAX функций для Power BI и Power Pivot на русском языке: скачать
3) [Мини-видеокурс] Большая работа с DAX функцией FILTER в Power BI и Power Pivot (скидка 50%): Перейти
 

Статья на основе видео (текстовая расшифровка)

Перед нами стоит задача: трансформировать исходную таблицу (продажи менеджеров за период) в таблицу, которая покажет нам по каждому месяцу ТОП-3 менеджеров, их продажи и ранг.

В качестве категорий мы будем использовать календарные месяцы из столбца [Дата].

Если мы внимательно посмотрим на исходную таблицу, то увидим, что есть месяцы, которым соответствует более, чем 3 значения. Нам же нужно получить ТОП-3 по каждому месяцу.

Прежде, чем перейти к разбору нашей задачи, я бы хотел вам напомнить, что в DAX решить одну и ту же задачу можно совершенно разными путями. Наш текущий способ будет таким, чтобы показать и объяснить вам определенный материал по DAX.

Итак, что нам необходимо сделать? Мы должны из большой исходной таблицы получить небольшую краткую таблицу, отфильтрованную по столбцу [Ранг], который содержит значения 1, 2 и 3.

Порядок работы:

1. В исходной таблице нужно создать новый столбец с рангами;
2. Отфильтровать таблицу с созданным столбцом по значениям этих рангов.

И всё!

Сложность заключается в создании этого дополнительного столбца.

Да, и еще один момент, в рамках распродажи до 29 ноября 2024 г. у Вас имеется возможность приобрести большой, пошаговый видеокурс «DAX — это просто» со скидкой 50% (вместо 10000, всего за 5000 руб.)

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

Итак, пользуйтесь этой возможностью, заказывайте курс «DAX — это просто» со скидкой 50% (до 29 ноября 2024 г.): узнать подробнее

до конца распродажи осталось:

 

Создание нового столбца с рангами

Переходим в вкладку Моделирование и создаем новый столбец.

 
Столбец мы назовем [Ранг]. С помощью функции RANKX рассчитаем в этом столбце ранг относительно продаж. Данная функция требует несколько параметров, но мы будем использовать только первые два.

Параметр 1: таблица, по которой нам нужно произвести ранги (в нашем случае это ‘Продажи’);
Параметр 2: выражение, по которому будут рассчитываться ранги (в нашем случае это столбец [Продажи]).

Нажимаем ОК и смотрим результат:

 
Столбец [Ранг] рассчитался, но он рассчитался относительно всей таблицы. А нам необходимо получить ранги значений столбца [Продажи] относительно категорий — месяцев в столбце [Дата].

Как это сделать? Нужно просто эту исходную таблицу отфильтровать.

 

Применение фильтра к таблице

Воспользуемся функцией FILTER.
Параметр 1: исходная таблица ‘Продажи’;
Параметр 2: категория, по которой мы будем фильтровать поданную на вход таблицу. Это столбец [Дата].

Но из всего значения даты нам нужен только месяц. И Power BI предоставляет нам такую возможность, подставив «Месяц» в автоматическом режиме:

'Продажи'[Дата].[Месяц]

И давайте сравним этот месяц, например, с месяцем «Сентябрь». Нажимаем ОК и смотрим результат.

 
Относительно категории «Сентябрь» ранги рассчитались верно, а относительно других категорий (месяцев) – неверно.

А что будет, если заменить «Сентябрь» на «Октябрь»?

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

Чтобы понять суть происходящего, давайте разберем как работает код DAX в этой формуле.

 
Когда DAX будет рассчитывать ранг для ячейки 1, то в качестве фильтра необходимо подать тот месяц, который находится в этой строке, а именно «Октябрь». И тогда ранг рассчитается верно.
А когда DAX будет рассчитывать ранг для ячейки 2, то в качестве фильтра необходимо подать «Ноябрь».

Указывая в качестве категории какой-либо конкретный месяц, мы устанавливаем фильтр как статический. А нам необходимо, чтобы данный фильтр динамически изменялся относительно той строки, в которой DAX находится, рассчитывая ранг для ячейки.

Вот мы и подошли к теме динамической фильтрации таблиц.

 

Алгоритм работы DAX функции FILTER в режиме динамической фильтрации

Как я уже сообщал выше, в контексте данного урока я расскажу лишь общие, поверхностные сведения по этой теме. Для более углубленного изучения динамической фильтрации вы можете пройти мини-видеокурс «Большая работа с функцией FILTER в Power BI и Power Pivot» (перейти).

Итак, рассмотрим код DAX, который производит динамическую фильтрацию:

 
Функция FILTER фильтрует таблицу, указанную в первом параметре, с помощью динамического фильтра, указанного во втором параметре. Динамический фильтр будет изменяться относительно строк, в которых DAX будет находиться, рассчитывая ячейки. Такое динамическое изменение фильтра происходит благодаря DAX-функции EARLIER.

А теперь внимание!
Таблица ‘Продажи’, указанная в первом параметре функции FILTER – это не та таблица, где находится DAX, рассчитывая ячейку. Это таблица ‘Продажи’ — есть новая виртуальная таблица, созданная в оперативной памяти компьютера. И эта виртуальная таблица будет фильтроваться по фильтру, указанному во втором параметре функции FILTER. А данный фильтр говорит, что столбец (‘Продажи'[Дата].Месяц) из виртуальной таблицы ‘Продажи’ должен равняться выражению функции EARLIER.

 
В выражение вставлен этот же столбец (‘Продажи'[Дата].Месяц), но так как он находится внутри функции EARLIER, то он принадлежит не виртуальной таблице, а внешней таблице, расположенной на один уровень выше, то есть таблице, в которой и происходит вычисление ранга.

Другими словами, помимо начальной таблицы функция FILTER создает виртуальную таблицу для каждого месяца, внутри которой и происходит ранжирование только для этого определенного месяца. А затем эти значения ранга возвращаются в рассчитываемый столбец [Ранг].

 

Расчет ТОП-3 по категориям (месяцам) используя дополнительный столбец

Итак, мы получили таблицу с рангами в разрезе каждого месяца. Остается только отфильтровать ее, оставив только ТОП-3 по продажам.

Создаем новую таблицу и назовем ее «ТОП 3 по категориям»

 
В первый параметр поместим нашу таблицу ‘Продажи’, а во второй фильтрующий параметр – столбец из этой таблицы [Ранг]. Но фильтровать нам необходимо по значениям 1 или 2, или 3.

Фильтр в режиме «или» очень удобно прописывать через оператор IN {}

 
Осталось только установить для столбца [Дата] тип данных как «Дата»

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

 

Чтобы рассчитать эту таблицу мы создали в исходной таблице столбец [Ранг]. В DAX лучше не создавать вычисляемые столбцы и таблицы, если можно обойтись без их создания. Все вычисляемые столбцы и таблицы хранятся в оперативной памяти компьютера, а она не бесконечна. Поэтому, чем больше вы создаете столбцов и таблиц, тем больше загружается оперативная память компьютера, тем медленнее будет работать ваш компьютер, а следовательно, медленнее будет работать ваш отчет.

 

Расчет ТОП-3 по категориям (месяцам) используя виртуальный столбец

В реальной таблице создавать столбец [Ранг] нет необходимости.
Давайте вырежем код этого столбца:

 
а сам этот столбец удалим из таблицы ‘Продажи’:

 
Код вырезанного столбца [Ранг] мы теперь вставим в код таблицы ‘ТОП 3 по категориям’.

Сейчас эта таблица не работает, т.к. она ссылается на не существующий теперь столбец [Ранг].

 
Столбец этот мы сейчас создадим не в реальной таблице, а в виртуальной. Воспользуемся DAX-функцией ADDCOLUMNS.

Параметр 1: исходная таблица ‘Продажи’;
Параметр 2: название создаваемого столбца «Ранг»;
Параметр 3: вставляем код создаваемого столбца, который мы вырезали ранее.

Нажимаем ОК и в полученной таблице произведем сортировку по возрастанию в столбце [Дата].

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

 

Алгоритм работы DAX функции ADDCOLUMNS

Функция ADDCOLUMNS возвращает виртуальную таблицу, указанную в первом параметре, и дополняет эту таблицу новыми столбцами, прописанными во втором и следующих параметрах. Этих столбцов мы можем создать несколько. Для этого в четных параметрах (2, 4, 6 и т.д.) мы прописываем названия этих столбцов, а в нечетных параметрах, начиная с третьего (3, 5, 7 и т.д.)  записываем их код.

В дальнейшем мы можем обращаться к этим созданным столбцам. В нашем примере DAX-функция FILTER в своем втором параметре обращается к столбцу [Ранг]. Так как наша таблица виртуальная, то к столбцу мы должны обращаться кратким именем, а именно без указания таблицы с именем столбца в квадратных скобках.

Данный код можно также повторить в Power Pivot, но есть нюанс.
Создать в Power Pivot такую таблицу не получится:

 

Потому что в Power Pivot создавать в модели данных таблицы нельзя.
Но код будет работать, но только внутри какого-нибудь другого кода. Например, если вам нужна какая-то мера и внутри ее нужна таблица, в которой необходимо рассчитать ранги относительно категорий, то такой код буде абсолютно точно работать и в Power Pivot.

Но что делать когда хочется и в Power Pivot вынести такую таблицу?
Я посвящу отдельный урок тому, как можно и в Power Pivot это сделать, но сделать через меры, чтобы они рассчитывались в динамическом режиме.

Так что следите за новыми уроками и мастер-классами!

Итак, мы полностью раскрыли тему расчета ранжирования относительно категорий внутри единой таблицы.

На этом всё. Пока!

---

---

 

Друзья, хотите изучить полный курс по языку DAX для Power BI и Power Pivot
со скидкой 50% вместо 10000 всего за 5000 р.?

 
акция действует до 29 ноября

 

 

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

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

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