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

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

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

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

Пусть у нас есть таблица с продажами 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 от Microsoft, но также и отечественная - Visiology.

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