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

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

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

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

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

Пусть у нас есть таблица с продажами Sales и уникальными номерами договоров продаж Sales [Order Number], а также таблица Customer с информацией о клиентах, рассчитаем коэффициент проникновения в зависимости от характеристик клиента.

Как же рассчитать коэффициент проникновения по клиентам в 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 для коэффициента проникновения по клиентам на dax.do

Также стоит отметить, что существует не только реализация DAX от Microsoft, но также и отечественная - Visiology.

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

Комментарии (2)


  1. Kryptonets
    16.09.2024 22:04
    +2

    Более производительной в некоторых сценариях альтернативой DISTINCTCOUNT(Table[Column]) может служить конструкция вида SUMX(DISTINCT(Table[Column]),1).


  1. koanse Автор
    16.09.2024 22:04

    Да, интересно, даже есть об этом материал, но обычно DISTINCTCOUNT быстрее

    There may be reports where the # Customer SUMX measure is way faster than the classic # Customer measure based on DISTINCTCOUNT, though usually the opposite is true.