Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):
- Подробное ВИДЕО «Расчет ТОП-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.
Порядок работы:
- В исходной таблице нужно создать новый столбец с рангами;
- Отфильтровать таблицу с созданным столбцом по значениям этих рангов.
И всё!
Сложность заключается в создании этого дополнительного столбца.
Создание нового столбца с рангами
Переходим в вкладку Моделирование и создаем новый столбец.
Столбец мы назовем [Ранг]. С помощью функции 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 это сделать, но сделать через меры, чтобы они рассчитывались в динамическом режиме.
Так что следите за новыми уроками и мастер-классами!
Итак, мы полностью раскрыли тему расчета ранжирования относительно категорий внутри единой таблицы.
На этом всё. Пока!
Пожалуйста, оцените статью:
Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»
Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.
Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.
Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D