TOPN товаров с помощью DAX в PowerBI

Ещё одна популярная задача, вопросы по которой часто встречаются в телеграмм-чате Power BI Group RU - подсчёт или отображение топовых значений. Это могут быть товары, клиенты, дни и так далее.

Хотя в заголовке я вынес подсчёт N-го количества товаров, но естественно это можно использовать для нужного вам измерения.

Итак, возьмём простую модель данных:

Задача: показать на линейчатой диаграмме #ТОП5 товаров по продажам.

Это очень просто сделать встроенными средствами.

Создаём диаграмму, добавляя в неё столбец 'Products'[ProductName] на ось, а в значения меру [Sum Orders Qty].

Выбираем на холсте визуальный элемент, далее открываем панель фильтров и в разделе фильтров визуального элемента для столбца 'Products'[ProductName] задаём Тип фильтра "Ведущие N". Выбираем сортировку "Сверху" или "Снизу" и сколько элементов показывать. Добавляем меру или столбец на основе значения которых рассчитывать и нажимаем "Применить фильтр"

Такой фильтр прекрасно работает - он реагирует на другие фильтры, отбирая #ТОП5 с учётом всех контекстов.

Такой подход удобно использовать, если вам надо всегда отображать фиксированное количество лучших/худших товаров.

Но встречаются и более сложные вопросы:

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

Чтобы ответить на эти и другие вопросы, нам необходимо будет написать специальные меры. Давайте разберём несколько примеров.

Какую долю от общего объема занимают продажи топовых товаров?

Для ответа на этот вопрос, как я сказал, нам необходимо будет написать собственную меру, которая будет считать объём продаж #ТОП5 товаров.

Итак, разберём по шагам, что происходит в этой мере.

1. Переменная _N - присваиваем ей значение сколько товаров будем выводить
2. Переменная _TOP - с помощью функции TOPN получаем таблицу топовых товаров: отбери 5 (_N) товаров из всего списка товаров - ALL(Products[ProductName]), отсортированного от большего к меньшему DESC по значению объёма продаж [Sum Orders Qty]
3. Переменная _Result - считаем сумму продаж по всему списку топовых товаров

Выведем полученную меру в таблицу:

Как видно на рисунке, мера в каждой строке возвращает одинаковое значение - сумму продаж первых пяти товаров. Это происходит потому, что в переменной _Result мы изменили контекст вычисления, передавая столбцу Products[ProductName] список топовых товаров вместо значения из текущей строки.

Не смотря на то, что внутри функции TOPN я беру список всех товаров, с помощью функции ALL, сумма у меня считается с учётом фильтра по категории. Это происходит по тому, что на третий аргумент функции TOPN - [Sum Orders Qty] по прежнему действуют все контексты фильтров, и для товаров, которые не входят в категорию Bikes просто возвращается пустое значение и они не попадают в топовые.

Для того, чтобы ответить на вопрос подзаголовка, создадим ещё одну меру:

Обратите внимание, что в качестве знаменателя я использую простую меру суммы объёма продаж, так как буду выводить значение меры в карточке, где не будет контекста по наименованию товаров, как в таблице, но хочу, чтобы влияли все фильтры, также как и при расчёте #ТОП5.

Если сравнить два последних скриншота, то можно увидеть, что #ТОП5 товаров в рамках категории Bikes занимают существенно меньшую долю, чем #ТОП5 товаров среди всех категорий, из чего можно сделать вывод, что в категории Bikes более "ровный" ассортимент без ярких лидеров, а вот в целом среди всех товаров есть пять товаров "хитов".

Как соотносится динамика продаж топовых товаров с общей динамикой?

Создадим визуализацию, на которой будем показывать динамику продаж - пусть будет "Гистограмма с группировкой"

Теперь в значения строк добавим меру TOPN ProductName v.1

Как видно, динамика практически идентичная, небольшая разница только в феврале и марте 2017 года.

Но усложним немного сценарий: необходимо показать динамику выбранного списка товаров в сравнении с динамикой товаров входящих в #ТОП5.

Получилась интересная картина. Если вы читаете внимательно, то помните, что когда мы вычисляем список топовых товаров в переменной _TOP, мы получаем список всех товаров с помощью функции ALL. И продажи топовых товаров больше, чем продажи выбранных товаров.

А есть ли пересечение этих двух списков? Чтобы это узнать немного модифицируем меру:

С помощью функции KEEPFILTERS сохраняем текущий контекст по столбцу Products[ProductName], вычисляя пересечение со списком _TOP.

На диаграмме видно, что пересечение есть и доля товаров из #ТОП5 составляет значительную долю в выбранном списке.

Вот как это выглядит в матричном виде - каждый месяц может быть свой список пересечений:

Точно также каждый месяц может быть свой #ТОП5 среди выбранных товаров.

Ещё немного изменим меру - для того, чтобы считать по списку среди выбранных, необходимо заменить функцию ALL на ALLSELECTED

Сравним полученный результат в матрице:

И тоже самое на диаграмме:

Как предоставить пользователю выбор количества топовых товаров с помощью среза?

И ещё один наиболее часто встречающийся вопрос.

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

Вспомогательную таблицу можно создать в PowerQuery или непосредственно в модели с помощью функции GENERATESERIES или DATATABLE. А также можно воспользоваться встроенным функционалом "What If" - "Что если", и создать параметр там. Главное отличие от самостоятельного создания таблицы - это дополнительный тип среза в виде ползунка с единичным выбором.

При этом данная таблица также создаётся с помощью функции GENERATESERIES. По мимо дополнительного типа среза при создании таблицы параметров сразу создаётся мера, которая возвращает выбранное в срезе значение.

Модифицируем нашу меру из статической в динамическую, для этого достаточно заменить определение переменной _N:

Сравним действие всех мер:

Я разобрал несколько примеров того, как можно использовать функцию TOPN в расчётах и визуализации. Пишите в комментариях свои идеи или пожелания, которые может быть у вас не получается реализовать для получения топовых значений. Или наоборот, которые вы реализовали и просто хотите поделиться этим.

Файл с примером можно скачать по ссылке.