Определяем доли и коэффициенты проникновения с помощью DAX08.11.2024 16:16

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

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

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

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

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

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

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

8187759496be0da32c82b8be3af7b69f.png

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

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

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

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

В том случае, если есть несколько таблиц-справочников, то можно использовать REMOVEFILTERS для расчета знаменателя долей. В качестве примера можно рассмотреть пример — коэффициент проникновения (https://habr.com/ru/articles/843628/).

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

\text{Коэффициент проникновения} = \frac{\text{Количество продаж заданного товара или услуги}}{\text{Общее количество продаж}}\cdot 100 \text{%}

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

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

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

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

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

9c8359d69997f0e6be916a1e492c77be.png

Видно, что сумма коэффициентов проникновения равна 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 в Ваших проектах!

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

© Habrahabr.ru