Преимущества DAX на примере коэффициента проникновения16.09.2024 11:30

Привет, Хабр! В рамках Business Intelligence для расчета KPI и других статистических характеристик могут использоваться различные средства. Универсальным и мощным инструментом является язык DAX, в этой статье я хочу показать его преимущества на примере популярной задачи расчета коэффициента проникновения в Power BI.

Если интересна аналитика с DAX — то добро пожаловать !)

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

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

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

Как же рассчитать коэффициент проникновения в DAX? По сравнению с SQL, нужно ли помнить всю схему данных, имена ключей таблиц, строить подзапросы и так далее? Нет, всего лишь создать меру Коэффициент проникновения по клиентам в пару строк в таблице Sales:

Коэффициент проникновения по клиентам := 100 * DIVIDE (
            DISTINCTCOUNT ( Sales[Order Number] ),
            CALCULATE ( DISTINCTCOUNT ( Sales[Order Number] ), REMOVEFILTERS ( Customer ) )
        )

В этой формуле производится деление с обработкой нулей в знаменателе с помощью DIVIDE, причем DISTINCTCOUNT (Sales[Order Number]) считает уникальные номера договоров продаж (с учетом текущей фильтрации в строке по Customer — «с учетом проникновения»), CALCULATE (DISTINCTCOUNT (Sales[Order Number]), REMOVEFILTERS (Customer)) считает все уникальные номера договоров продаж (без «фильтров проникновения» по Customer).

Теперь в PBI мы можем построить практически любой виджет, и использовать поля из таблицы Customer, например, Education, и созданную меру Коэффициент проникновения по клиентам.

Расчет коэффициента проникновения при помощи меры Коэффициент проникновения по клиентам

Расчет коэффициента проникновения при помощи меры Коэффициент проникновения по клиентам

Если посмотреть немного глубже, на DAX, который генерируется виджетом, то его примерный вид следующий:

EVALUATE
SUMMARIZECOLUMNS (
    Customer[Education],
    "Коэффициент проникновения по клиентам",
        100 * DIVIDE (
            DISTINCTCOUNT ( Sales[Order Number] ),
            CALCULATE ( DISTINCTCOUNT ( Sales[Order Number] ), REMOVEFILTERS ( Customer ) )
        )
)
ORDER BY [Penetration Rate] DESC

Здесь видно, что REMOVEFILTERS (Customer)) снимает фильтр со строки, который генерируется полем группировки Customer[Education] из SUMMARIZECOLUMNS, и становится понятна идея создания и использования меры Коэффициент проникновения по клиентам.

Также пример доступен онлайн в dax.do, где видно удобство круговой диаграммы для отображения коэффициентов проникновения, т.к. по сути несколько коэффициентов проникновения образуют полную группу событий.

Пример с полным DAX для коэффициента проникновения по клиентам на dax.do

Надеюсь, такой реальный пример использования DAX окажется полезен :)