15 формул DAX для Power BI: расчет метрик и анализ данных

Для кого эта статья:

• Начинающие и опытные аналитики данных
• Специалисты, желающие улучшить свои навыки в DAX и Power BI

• Люди, заинтересованные в аналитике и визуализации бизнес-данных

  DAX — язык, превращающий Power BI из простого инструмента визуализации в мощную аналитическую платформу. Освоив 15 ключевых формул DAX, вы сможете решать сложные бизнес-задачи: рассчитывать нарастающие итоги, анализировать тренды, создавать динамические KPI, которые автоматически обновляются при новых данных. Эти формулы — как швейцарский нож для аналитика, позволяющий превратить сырые данные в управленческие решения. Если раньше вы "плавали" в DAX или избегали его, самое время раскрыть его потенциал. 🚀

Хотите освоить DAX и стать востребованным BI-аналитиком? Обучение BI-аналитике от Skypro погружает в практику работы с реальными кейсами в Power BI. За 9 месяцев вы научитесь строить интерактивные дашборды, создавать сложные DAX-формулы и анализировать бизнес-данные. Программа разработана действующими аналитиками, которые делятся профессиональными секретами использования DAX в крупных компаниях.

Основы DAX в Power BI: 15 формул для практического применения

DAX (Data Analysis Expressions) представляет собой формульный язык, разработанный Microsoft специально для работы с табличными моделями данных. В основе Power BI лежит именно этот язык, позволяющий создавать расчетные столбцы и меры, которые трансформируют ваши данные в ценную аналитическую информацию.

Освоение DAX может показаться сложной задачей для новичков, но начав с базовых формул, вы сможете постепенно перейти к более сложным конструкциям. Давайте рассмотрим 15 ключевых формул DAX, которые помогут решить большинство аналитических задач в Power BI:

1. SUM – Суммирование значений в столбце: SUM(Sales[Amount])
2. AVERAGE – Расчет среднего значения: AVERAGE(Sales[Amount])
3. COUNT – Подсчет количества строк: COUNT(Customers[CustomerID])
4. DISTINCTCOUNT – Подсчет уникальных значений: DISTINCTCOUNT(Sales[CustomerID])
5. CALCULATE – Изменение контекста вычислений: CALCULATE(SUM(Sales[Amount]), Products[Category]="Electronics")
6. FILTER – Фильтрация данных: FILTER(Sales, Sales[Amount] > 1000)
7. RELATED – Доступ к связанным таблицам: RELATED(Products[Category])
8. DIVIDE – Безопасное деление с защитой от нуля: DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue]), 0)
9. IF – Условные выражения: IF(Sales[Amount] > 1000, "High", "Low")
10. SUMX – Итерация с суммированием: SUMX(Sales, Sales[Quantity] * Sales[Price])
11. DATEADD – Манипуляции с датами: DATEADD(Calendar[Date], -1, MONTH)
12. SAMEPERIODLASTYEAR – Сравнение с прошлым годом: SAMEPERIODLASTYEAR(Calendar[Date])
13. RANKX – Ранжирование значений: RANKX(ALL(Products), SUM(Sales[Amount]))
14. ALL – Удаление фильтров: ALL(Calendar)
15. TOTALYTD – Расчет нарастающего итога за год: TOTALYTD(SUM(Sales[Amount]), Calendar[Date])

Эти формулы составляют основу DAX и позволяют создавать большинство необходимых расчетов в бизнес-аналитике. Комбинируя их, вы сможете решать даже сложные аналитические задачи.

Александр Петров, BI-аналитик Когда я начал работать с крупным ритейлером, передо мной стояла задача создать отчет о динамике продаж с разбивкой по товарным категориям. Руководство хотело видеть не просто абсолютные цифры, но и процентное соотношение категорий в общем объеме продаж, а также динамику изменений по сравнению с предыдущими периодами.

Изначально я пытался решить эту задачу с помощью сводных таблиц в Excel, но процесс был трудоемким и требовал постоянного обновления вручную. Когда я перешел на Power BI и начал использовать DAX, все изменилось.

Особенно полезной оказалась формула DIVIDE в сочетании с CALCULATE и ALL:

Доля категории = 
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(
SUM(Sales[Amount]),
ALL(Products[Category])
)
)

Эта формула автоматически рассчитывала долю каждой категории в общих продажах с учетом всех применяемых фильтров. А для расчета динамики я использовал SAMEPERIODLASTYEAR:

Динамика YoY = 
DIVIDE(
SUM(Sales[Amount]) – CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date])),
CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date]))
)

Отчет, который раньше занимал у меня два дня работы каждый месяц, теперь обновлялся одним кликом. Руководство получило интерактивный дашборд с возможностью детализации данных до уровня отдельных товаров.

Базовые функции DAX для аналитики в Power BI

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

Создание базовой меры в Power BI начинается с выбора таблицы, нажатия правой кнопки мыши и выбора "Новая мера". Затем вводится формула DAX, например:

Общая выручка = SUM(Sales[Revenue])

Для создания расчетного столбца, выберите таблицу, нажмите "Добавить столбец" и введите формулу:

Прибыль = Sales[Revenue] – Sales[Cost]

Ключевое отличие мер от расчетных столбцов заключается в контексте вычислений:

• Расчетные столбцы вычисляются во время загрузки модели и добавляют данные к каждой строке таблицы
• Меры вычисляются динамически в момент визуализации данных с учетом примененных фильтров

Для более сложных расчетов важно понимать концепцию контекста в DAX:

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

Функция CALCULATE является одной из самых мощных в DAX, так как позволяет изменять контекст фильтра:

Выручка от электроники = CALCULATE(SUM(Sales[Revenue]), Products[Category]="Electronics")

Эта формула вычисляет сумму выручки только для категории "Electronics", независимо от выбранных фильтров в отчете.

Для анализа отношений между различными показателями часто используются относительные метрики:

Маржинальность = DIVIDE(SUM(Sales[Profit]), SUM(Sales[Revenue]), 0)

Здесь функция DIVIDE не только выполняет деление, но и предотвращает ошибки при делении на ноль, возвращая 0 (третий аргумент) в таких случаях.

Продвинутые формулы DAX для фильтрации и группировки

После освоения базовых функций DAX, самое время перейти к более сложным конструкциям, которые открывают мощные возможности для фильтрации и группировки данных. Именно здесь проявляется настоящая сила DAX как языка бизнес-аналитики. 📊

Функция FILTER является одной из фундаментальных для создания динамических наборов данных:

Высокомаржинальные продажи = CALCULATE(SUM(Sales[Revenue]), FILTER(Sales, Sales[Margin] > 0.4))

Эта формула вычисляет сумму выручки только для тех продаж, где маржа превышает 40%. FILTER возвращает таблицу, а CALCULATE применяет её как контекст фильтра.

Для более сложной фильтрации с несколькими условиями можно использовать вложенные функции FILTER:

Премиум-продажи = 
CALCULATE(
SUM(Sales[Revenue]), 
FILTER(
FILTER(Sales, Sales[Amount] > 1000),
RELATED(Products[Category]) = "Premium"
)
)

Функция ALL играет ключевую роль при создании сравнительных метрик, так как позволяет временно снять фильтры:

Доля в общей выручке = 
DIVIDE(
SUM(Sales[Revenue]), 
CALCULATE(SUM(Sales[Revenue]), ALL(Products))
)

Эта формула рассчитывает долю текущей выборки в общей выручке по всем продуктам, независимо от выбранных фильтров.

Варианты функции ALL включают:

• ALL(Table) – снимает все фильтры с таблицы
• ALL(Column) – снимает фильтры только с указанного столбца
• ALLEXCEPT(Table, Column1, Column2...) – снимает все фильтры с таблицы, кроме указанных столбцов

Для группировки и ранжирования данных используется мощная функция RANKX:

Ранг продукта по выручке = 
RANKX(
ALL(Products[ProductName]), 
CALCULATE(SUM(Sales[Revenue])),
,
DESC
)

Эта формула ранжирует продукты по сумме выручки в порядке убывания.

Для сегментации данных на основе пороговых значений можно использовать комбинацию SWITCH и CALCULATE:

Категория клиента = 
SWITCH(
TRUE(),
SUM(Sales[Revenue]) > 100000, "VIP",
SUM(Sales[Revenue]) > 50000, "Premium",
SUM(Sales[Revenue]) > 10000, "Standard",
"Basic"
)

Для анализа вклада каждого элемента можно использовать функцию ADDCOLUMNS в сочетании с SUMMARIZE:

Топ-категории = 
ADDCOLUMNS(
SUMMARIZE(
Products,
Products[Category],
"Выручка", SUM(Sales[Revenue])
),
"Доля", DIVIDE([Выручка], CALCULATE(SUM(Sales[Revenue]), ALL(Products[Category])))
)

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

Ключевой проблемой было отсутствие возможности быстро фильтровать кампании по комплексным критериям — например, найти кампании с высоким показом, но низким CTR и высокой стоимостью привлечения клиента.

Решение пришло, когда я начала использовать продвинутые функции фильтрации DAX. Особенно полезной оказалась комбинация FILTER и ALL:

Проблемные кампании = 
CALCULATE(
COUNTROWS(Campaigns),
FILTER(
ALL(Campaigns),
CALCULATE(AVG(Campaigns[Impressions])) > 10000 &&
CALCULATE(AVG(Campaigns[CTR])) < 0.02 &&
CALCULATE(AVG(Campaigns[CPA])) > 50
)
)

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

Я пошла дальше и создала систему автоматического ранжирования кампаний с помощью RANKX:

Ранг эффективности = 
RANKX(
ALL(Campaigns[CampaignName]),
DIVIDE(SUM(Campaigns[Conversions]), SUM(Campaigns[Cost])),
,
DESC
)

Это позволило нам создать интерактивный дашборд, где маркетологи могли в реальном времени видеть, какие кампании работают лучше всего по соотношению конверсий к затратам.

В результате, время на анализ эффективности сократилось с нескольких дней до нескольких минут, а перераспределение бюджетов стало происходить в 3 раза быстрее. За первый квартал использования этого подхода мы смогли повысить общую рентабельность рекламных инвестиций на 27%.

Временные расчеты в DAX: работа с датами и периодами

Анализ данных во временной динамике — одна из ключевых задач в бизнес-аналитике. DAX предлагает мощный набор функций для работы с датами и периодами, позволяющий создавать сложные временные сравнения и расчеты нарастающих итогов. 📅

Основой для временного анализа в Power BI служит таблица дат, которую рекомендуется создавать с помощью DAX:

Календарь = CALENDAR(DATE(2020, 1, 1), DATE(2025, 12, 31))

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

• Год: Год = YEAR(Calendar[Date])
• Месяц: Месяц = FORMAT(Calendar[Date], "MMMM")
• Номер месяца: НомерМесяца = MONTH(Calendar[Date])
• Квартал: Квартал = "Q" & QUARTER(Calendar[Date])
• День недели: ДеньНедели = FORMAT(Calendar[Date], "dddd")
• Рабочий день: РабочийДень = IF(WEEKDAY(Calendar[Date], 2) < 6, "Да", "Нет")

Функция DATEADD позволяет сдвигать даты для сравнения с предыдущими периодами:

Выручка прошлого месяца = 
CALCULATE(
SUM(Sales[Revenue]), 
DATEADD(Calendar[Date], -1, MONTH)
)

Для более сложных сравнений с аналогичными периодами прошлых лет используется SAMEPERIODLASTYEAR:

Выручка YoY = 
CALCULATE(
SUM(Sales[Revenue]), 
SAMEPERIODLASTYEAR(Calendar[Date])
)

Для расчета динамики изменений часто используется комбинация этих функций:

Прирост YoY, % = 
DIVIDE(
SUM(Sales[Revenue]) – CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Calendar[Date])),
CALCULATE(SUM(Sales[Revenue]), SAMEPERIODLASTYEAR(Calendar[Date])),
0
) * 100

Расчет нарастающих итогов можно выполнить с помощью функций временной интеллектуальности:

Для более гибкого анализа можно создать скользящие периоды с помощью функций DATESBETWEEN и DATESINPERIOD:

Выручка за последние 90 дней = 
CALCULATE(
SUM(Sales[Revenue]),
DATESBETWEEN(
Calendar[Date],
MAX(Calendar[Date]) – 90,
MAX(Calendar[Date])
)
)

Для сравнения периодов разной длительности полезна функция DATESINPERIOD:

Выручка за последний полный месяц = 
CALCULATE(
SUM(Sales[Revenue]),
DATESINPERIOD(
Calendar[Date],
ENDOFMONTH(MAX(Calendar[Date])) – 1,
-1,
MONTH
)
)

Использование временной интеллектуальности DAX позволяет создавать динамические отчеты, автоматически адаптирующиеся к текущей дате и выбранным периодам, что значительно повышает аналитические возможности Power BI. 🚀

Визуализация бизнес-метрик с помощью DAX-выражений

Создание эффективных визуализаций в Power BI напрямую зависит от правильного использования DAX для расчета бизнес-метрик. Правильно построенные метрики делают дашборды не просто красивыми, но и действительно полезными для принятия бизнес-решений. 📈

Одна из ключевых задач бизнес-аналитики — расчет KPI (ключевых показателей эффективности). DAX позволяет создавать динамические KPI, которые автоматически сравнивают текущие значения с целевыми:

% выполнения плана = 
DIVIDE(
SUM(Sales[Revenue]),
SUM(Targets[TargetRevenue]),
0
) * 100

Для визуализации отклонений от целевых показателей можно использовать условное форматирование на основе DAX-мер:

Статус выполнения плана = 
IF(
[% выполнения плана] >= 100, 
"Выполнен", 
IF(
[% выполнения плана] >= 90, 
"Почти выполнен", 
"Не выполнен"
)
)

Анализ вклада различных компонентов в общий результат является важной частью бизнес-аналитики. DAX позволяет рассчитывать такие метрики:

Вклад категории в общую выручку = 
DIVIDE(
SUM(Sales[Revenue]),
CALCULATE(
SUM(Sales[Revenue]),
ALL(Products[Category])
),
0
) * 100

Для выявления аномалий и выбросов в данных можно использовать статистические функции DAX:

Отклонение от среднего = 
DIVIDE(
ABS(SUM(Sales[Revenue]) – AVERAGE(Sales[Revenue])),
STDEV.P(Sales[Revenue]),
0
)

Важный аспект визуализации — создание пользовательских метрик на основе бизнес-логики. Например, сегментация клиентов на основе RFM-анализа:

RFM-сегмент = 
VAR R = [Recency Score]
VAR F = [Frequency Score]
VAR M = [Monetary Score]
RETURN
SWITCH(
TRUE(),
R >= 4 && F >= 4 && M >= 4, "VIP-клиенты",
R >= 3 && F >= 3 && M >= 3, "Лояльные клиенты",
R < 2 && F < 2 && M < 2, "Потерянные клиенты",
"Стандартные клиенты"
)

Для создания динамических заголовков и подписей в визуализациях можно использовать текстовые конкатенации в DAX:

Заголовок отчета = 
"Анализ продаж за " & 
FORMAT(MAX(Calendar[Date]), "MMMM yyyy") & 
" (рост " & 
FORMAT([Прирост YoY, %], "0.0%") & 
" к прошлому году)"

DAX также позволяет создавать динамические пороговые значения для визуализаций:

Верхний порог = 
VAR MeanValue = AVERAGE(Sales[Revenue])
VAR StdDev = STDEV.P(Sales[Revenue])
RETURN MeanValue + 2 * StdDev

Для комплексного анализа эффективности можно создать составные метрики, учитывающие несколько параметров:

Индекс эффективности = 
([Выполнение плана, %] * 0.4) + 
([Прирост YoY, %] * 0.3) + 
([Маржинальность, %] * 0.3)

Важным аспектом визуализации является контекстная фильтрация. DAX позволяет создавать динамические наборы данных для фокусировки на ключевых аспектах:

Топ-10 продуктов = 
TOPN(
10,
ALL(Products[ProductName]),
CALCULATE(SUM(Sales[Revenue]))
)

С помощью DAX можно также создавать метрики для оценки тенденций и прогнозирования:

Тренд выручки = 
VAR CurrentPeriod = SUM(Sales[Revenue])
VAR PreviousPeriod = CALCULATE(SUM(Sales[Revenue]), DATEADD(Calendar[Date], -1, MONTH))
VAR PriorPeriod = CALCULATE(SUM(Sales[Revenue]), DATEADD(Calendar[Date], -2, MONTH))
RETURN
IF(
CurrentPeriod > PreviousPeriod && PreviousPeriod > PriorPeriod,
"Устойчивый рост",
IF(
CurrentPeriod < PreviousPeriod && PreviousPeriod < PriorPeriod,
"Устойчивое падение",
"Нестабильная динамика"
)
)

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

Освоение 15 ключевых формул DAX — это не просто техническое умение, а стратегический актив для любого аналитика. Эти формулы трансформируют ваш подход к анализу данных, позволяя создавать динамические отчеты, которые адаптируются к контексту бизнеса и автоматически обновляются при изменении данных. Инвестировав время в освоение DAX, вы получаете инструмент, который сокращает часы рутинной работы до минут и открывает новые горизонты для глубокого анализа информации. Начните применять эти формулы поэтапно, и вы увидите, как ваши отчеты в Power BI обретают новый уровень ценности для бизнеса.

Читайте также

• Примеры использования Power Query
• SQL запросы и Power Query: основы и интеграция
• Лучшие книги по Power BI: самоучители для эффективной аналитики
• Работа с источниками данных в Power BI
• Power BI: как превратить данные в бизнес-инсайты за пару часов
• Интеграция SQL и Power Query: примеры запросов
• Визуализация данных в Power BI
• DAX в Power BI: освоение языка формул для продвинутой аналитики
• Основы SQL для работы с Power Query
• Бесплатное обучение Power BI: 15 проверенных ресурсов для аналитика