Определяем доли и коэффициенты проникновения с помощью DAX

Привет, Хабр! Одной из важных задач в аналитических запросах является расчет долей, который позволяет узнать, какая часть записей из общего количества по всей таблице соответствует какому-либо критерию. Также нередко полезными оказываются коэффициенты проникновения (в общем-то тоже являющиеся долями). Они позволяют оценить продажи, найти взаимосвязи признаков и сделать много еще чего полезного. Чтобы проводить такого рода расчеты идеально подходит язык DAX. Если Вам интересно, насколько это удобно и как именно сделать это в DAX — добро пожаловать под кат 🙂

Использование FILTER для расчета числителя доли

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

В качестве примера рассмотрим таблицу worker с полями worker[id] сотрудника, его пол worker[gender], имя worker[name] и должность worker[position].

Создадим меры для подсчета долей мужчин и женщин:

Доля М в % = 100     * DIVIDE (         COUNTX ( FILTER ( worker, worker[gender] = "м" ), worker[id] ),         COUNT ( worker[id] )     ) Доля Ж в % = 100     * DIVIDE (         COUNTX ( FILTER ( worker, worker[gender] = "ж" ), worker[id] ),         COUNT ( worker[id] )     )

В результате можно построить дашборд такого вида.

По сути, с учетом подстановки мер, такой дашборд генерирует DAX следующего вида:

SUMMARIZECOLUMNS (     'worker'[position],     "Доля М в %",         100             * DIVIDE (                 COUNTX ( FILTER ( 'worker', 'worker'[gender] = "м" ), 'worker'[id] ),                 COUNT ( 'worker'[id] )             ),     "Доля Ж в %",         100             * DIVIDE (                 COUNTX ( FILTER ( 'worker', 'worker'[gender] = "ж" ), 'worker'[id] ),                 COUNT ( 'worker'[id] )             ) )

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

Использование REMOVEFILTERS для расчета знаменателя доли

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

Коэффициент проникновения (или уровень проникновения) представляет собой процент случаев из всей совокупности, для которых была продажа товара или услуги:

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

Пусть у нас есть таблица с продажами sales и уникальными номерами договоров продаж sales[ordernumber], а также таблица customers с информацией о клиентах и полем образование customer[education] для анализа, рассчитаем коэффициент проникновения в зависимости от характеристик клиента.

Создадим меру Коэффициент проникновения по клиентам в таблице sales:

Коэффициент проникновения по клиентам = 100 * DIVIDE (             DISTINCTCOUNT ( sales[ordernumber] ),             CALCULATE ( DISTINCTCOUNT ( sales[ordernumber] ),                 REMOVEFILTERS ( customer ) ) )

На основе этой меры рассчитаем коэффициент проникновения по образованию клиента customers[education], для этого создадим дашборд с мерой Коэффициент проникновения по клиентам.

Видно, что сумма коэффициентов проникновения равна 100, т.к. есть все данные по customers[education] и события составляют полную группу событий.

При выполнении этой меры получим DAX вида:

SUMMARIZECOLUMNS (    customers[education],     "Коэффициент проникновения по клиентам",         100 * DIVIDE (             DISTINCTCOUNT ( sales[ordernumber] ),             CALCULATE ( DISTINCTCOUNT ( sales[ordernumber] ),                 REMOVEFILTERS ( customer ) )         ) )

Таким способом можно гибко выбирать характеристики клиента и получать значения коэффициента проникновения по ним.

Опасайтесь вложенных SUMMARIZECOLUMNS

В примерах выше вы наверняка заметили, что в выкладках DAX используется функция SUMMARIZECOLUMNS. Но я бы советовал обращаться с ней осторожно. Если у вас только одна таблица, то использовать SUMMARIZE или SUMMARIZECOLUMNS для знаменателя может быть не очень хорошей практикой с точки зрения производительности, если есть другие возможности.

Вообще говоря, SUMMARIZECOLUMNS является оптимизированной заменой SUMMARIZE. Поэтому использование SUMMARIZE для суммирования и подсчета знаменателя может вызывать вопросы.

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

Заключение

Работа с долями и коэффициентами проникновения — еще один пример, когда использование DAX делает решение аналитических задач проще и элегантнее. Вы можете применять эти инструменты на платформе Visiology 3 без ограничений — точно так же, как в Power BI. Я специально рассмотрел простейшие примеры и, надеюсь, они будут полезны Вам при расчете долей с помощью мер в DAX в Ваших проектах!

Успешных и быстрых дашбордов! 🙂