Чтобы превратить отчеты Power BI и Excel в настоящий инструмент аналитики, вам потребуются DAX-формулы. Используя эти формулы, можно настраивать вычисления в отчетах один раз, а при изменении исходных данных в отчете показатели будут пересчитаны. В Excel для этого потребуется нажать только одну кнопку — «Обновить все».
В этой статье мы собрали самые полезные и часто встречающиеся DAX-формулы. Какие именно формулы выбрали? Все, что доступно пользователю, перечислять смысла нет. А вот несколько формул, которые позволят решить 80% задач аналитики и пригодятся для оформления отчетов, рассмотреть стоит. Например, как показать остатки на конец периода, а не сумму всех остатков, как обычно получается в сводной таблице. Или как посчитать нарастающие итоги.
Кстати, в предыдущей статье «Введение в вычисления DAX» есть примеры других полезных и более простых формул – SUM, MIN, MAX, DIVIDE, BLANK и др. Если вы ещё не читали эту статью, советуем начать с неё.
Полезные материалы:
Для удобства формулы в статье разделены на две части.
Часто применяемые DAX-формулы:
Формулы для развернутой аналитики:
1. CALCULATE
CALCULATE рассчитывает выражение с учетом измененного контекста фильтра.
Это самая главная формула в языке DAX. Ее название так и переводится – «вычислить», поэтому без такой формулы отчеты точно сделать не получится. Записывается формула просто:
CALCULATE ( <выражение> [, <фильтр1>, <фильтр2> … ] )
выражение | вычисляемое выражение |
фильтр 1, фильтр 2… | логические фильтры, табличные выражения или функции изменения фильтра (модификаторы) |
В вычислениях CALCULATE часто используют вместе с функциями фильтрации, например, с FILTER. Или с модификаторами – USERELATIONSHIP, REMOVEFILTERS и др. Что это за функции и как они работают – смотрите дальше.
Пример: посчитать выручку по заказам в указанном регионе.
выручка Юг = CALCULATE ( SUM( ' заказы' [выручка]),' заказы' [регион] = " Юг" ) |
2. FILTER
FILTER выбирает данные в таблице или в выражении по заданному критерию. Результат ее вычисления — таблица. Синтаксис функции:
FILTER ( <таблица>, <фильтр> )
таблица | таблица, из которой выбираются данные (или выражение, определяющее таблицу) |
фильтр | логическое выражение, с помощью которого выполняется отбор |
В мерах FILTER обычно используют вместе с другими формулами, например, вместе с CALCULATE, CONTROWS и др.
Примеры:
1) Найти количество заказов по региону «Юг».
заказы Юг = COUNTROWS ( FILTER ( ' заказы' , ' заказы' [регион] = " Юг" ) ) |
С помощью формулы FILTER выбираются заказы по региону Юг, а CONTROWS считает, сколько таких заказов было.
2) Найти сумму убытков по заказам, в которых выручка меньше расходов.
заказы в минус = CALCULATE ( [выручка] – [расходы], FILTER ( ' заказы' , ' заказы' [выручка] < ' заказы' [расходы] )) |
3. ALL
В отличие от FILTER, которая ограничивает выборку данных, функция ALL делает наоборот. ALL снимает примененные к таблице или столбцу фильтры, и позволяет создавать вычисления по всем строкам таблицы или по всем значениям столбца. Записывается формула просто:
ALL ( <таблица> или <столбец> [, <столбец1> ,<столбец2>, … ] )
таблица | таблица, в которой нужно очистить фильтры (не может быть выражением) |
столбец | столбец, в котором нужно очистить фильтры (не может быть выражением) |
Так же как FILTER, функция ALL используется с другими формулами, например с CALCULATE. Если вам нужно убрать фильтры не со всей таблицы полностью, а только с некоторых столбцов, можно воспользоваться похожей формулой – ALLEXCEPT. С помощью ALL можно делать различные вычисления – найти долю от общего объема продаж, соотношения или проценты и т.д.
Пример: найти долю выручки от продаж товара.
Доля, % = DIVIDE ( [выручка], CALCULATE ( [выручка], ALL( ' спТовары' ) ) ) |
С помощью DIVIDE выручка по товару делится на общий итог от продаж. Для расчета общего итога как раз и используется формула ALL – она снимает все фильтры, примененные к справочнику товаров. Точно такой же результат получится, если вместо ALL использовать функцию REMOVEFILTERS, о которой написано дальше.
Поведение функции ALL можно проверить с помощью среза по товарам. Если в срезе поставить галочки рядом с некоторыми товарами, то видно, что сумма процентов в итоговой строке таблицы не равна 100%. Доля товара считается без учета того, выбраны или нет товары в срезе. Если нужно посчитать долю только с учетом выбранных товаров – так, чтобы итоговая сумма процентов в таблице всегда была равна 100%, вместо ALL используйте ALLSELECTED.
4. SUMX
Для чего нужна формула SUMX? Она позволяет суммировать значения выражения по строкам. Синтаксис формулы:
SUMX ( <таблица>, <выражение> )
таблица | таблица (или выражение, определяющее таблицу) |
выражение | выражение для каждой строки таблицы |
Если вам нужно просто посчитать сумму по столбцу, используйте формулу SUM. Если нужно рассчитать построчно показатели в таблице и затем сложить их, вам потребуется SUMX.
Самый простой пример вычислений с SUMX – расчет суммы продаж по формуле = количество х цена:
продажи = SUMX ( ' продажи' ,' продажи' [количество] * ' продажи' [цена] ) |
В таблице в каждой строке количество умножается на цену, а полученные результаты суммируются. В данном случае формула SUMX сработала очень похоже на «обычную» формулу Excel – СУММПРОИЗВ.
5. CALENDARAUTO
Таблица, без которой точно не обойдется ни один отчет в Power BI и Power Pivot – это таблица дат, Календарь. С помощью такой таблицы можно создавать аналитику по периодам и делать вычисления, связанные с датами.
Календарь можно создать несколькими способами, самый простой из которых в Power BI – сделать вычисляемую таблицу с помощью формулы CALENDARAUTO. Эта формула автоматически найдет первую и последнюю дату и создаст справочник.
Создается календарь (таблица дат) в Power BI просто:
Календарь = CALENDARAUTO() |
Также таблицу дат в Power BI можно создать с помощью CALENDAR, но в этой формуле потребуется указать границы – наибольшую и наименьшую дату.
А вот в Power Pivot в Excel таких формул для календаря нет. Так же, как нет возможности создавать вычисляемые таблицы. Поэтому календарь в Power Pivot можно сделать с помощью специальной кнопки «Таблица дат» (для этого даже предусмотрена специальная кнопка) или с помощью загрузки из Power Query.
6. FORMAT
Для создания «правильной» таблицы дат в Power BI вам пригодится формула FORMAT. Функция FORMAT преобразует значение в текст в указанном формате. Это аналог формулы ТЕКСТ в «обычном» Excel.
Синтаксис формулы:
FORMAT ( <значение>, <формат строки> )
значение | значение (или выражение, результат которого — единственное значение) |
формат строки | шаблон форматирования |
Пример: определить дни недели для дат.
День недели = FORMAT ( [Дата], " DDD" ) |
С помощью формулы FORMAT и шаблона "
DDD"
в созданном вычисляемом столбце записаны даты в виде сокращенных названий дней недели – пн, вт, ср и т.д. Кроме шаблона форматирования "
DDD"
в формулу можно добавить другие форматы, например, "
DDDD"
для отображения дней недели полностью (понедельник, вторник и т. д.)
Примеры форматов строк для дат, записываются в кавычках:
D | день месяца в виде числа без ведущих нулей (например, 1) |
DD | день месяца в виде числа с ведущими нулями (например, 01) |
DDD | сокращенное название дня недели (пн, вт, ср, …) |
DDDD | полное название дня недели (например, воскресенье) |
M | месяц в виде числа без ведущих нулей (например, январь — число 1) |
MM | месяц в виде числа с ведущими нулями (например, 01) |
MMM | сокращенное название месяца (янв) |
MMMM | полное название месяца (январь) |
YY | год в виде двух цифр |
YYYY | год в формате из четырех цифр |
Кстати, формула FORMAT также используется, чтобы создавать подробные подписи к диаграммам.
Полезные ссылки:
7. DATEADD
С помощью DATEADD можно сравнивать показатели разных периодов, например, текущий и прошлый год. Эта функция возвращает таблицу с набором дат, смещенных в будущее или в прошлое на указанное число интервалов.
Синтаксис формулы:
DATEADD ( <даты>, <количество интервалов>, <вид интервала> )
даты | столбец с датами |
количество интервалов | число интервалов, на которое нужно сдвинуть исходные даты |
вид интервала | вид интервала, который может быть следующим: YEAR, QUARTER, MONTH, DAY |
Хотя с помощью формулы DATEADD можно посчитать значения предыдущего дня, так лучше не делать – можно просто вычесть или прибавить к дате в формуле нужное число дней.
Пример: рассчитать значение показателя за прошлый год.
продажи пр.год = CALCULATE ( [продажи], DATEADD ( ' Календарь' [Date], -1, YEAR )) |
8. TOTALYTD
Формула TOTALYTD вычисляет значение выражения с начала года в текущем контексте. То есть с помощью TOTALYTD можно посчитать нарастающий итог по показателю за год. Чтобы рассчитать нарастающие итоги по кварталам или месяцам, вам потребуются похожие формулы: TOTALQTD для квартала и TOTALMTD для месяца.
Записывается формула просто:
TOTALYTD ( <выражение>, <даты> [, <фильтр> ] [, <конец года>] )
TOTALQTD ( <выражение>, <даты> [, <фильтр> ] )
TOTALMTD ( <выражение>, <даты> [, <фильтр> ] )
выражение | выражение, рассчитывающее скалярное значение |
даты | столбец дат (или выражение, его определяющее) |
фильтр | (необязательно) выражение, задающее фильтр |
конец года | (необязательно) дата окончания года, вводится в кавычках, по умолчанию 31 декабря |
Пример. Посчитать выручку нарастающим итогом за квартал и год.
выручка QTD квартал = TOTALQTD ( [выручка], ' Календарь' [Date] )выручка YTD год = TOTALYTD ( [выручка], ' Календарь' [Date] ) |
9. LASTNONBLANK
LASTNONBLANK определяет последнее непустое значение в столбце. Чтобы найти первое непустое значение в столбце – FIRSTNONBLANK. Синтаксис формул:
FIRSTNONBLANK ( <столбец>, <выражение> )
LASTNONBLANK ( <столбец>, <выражение> )
столбец | столбец (или выражение, возвращающее один столбец) |
выражение | выражение, в котором проверяется наличие пустых значений в указанном столбце |
С помощью этих функций, например, можно посчитать остатки на начало и конец периода. Формула LASTNONBLANK подходит для вычислений, когда остатки не указаны на последний день периода.
Пример. Найти остатки ТМЦ на конец периода.
Остаток = CALCULATE ( SUM ( ' данные' [остаток ТМЦ] ),LASTNONBLANK ( ' Календарь' [Date],CALCULATE ( SUM ( ' данные' [Дата]) )) ) |
10. CLOSINGBALANCEYEAR
CLOSINGBALANCEYEAR вычисляет значения выражения для последней даты года. Аналогично расчет для последней даты квартала – CLOSINGBALANCEQUARTER, для месяца — CLOSINGBALANCEMONTH. Так можно рассчитать остатки товаров или денег на счете на конец периода.
CLOSINGBALANCEYEAR ( <выражение>, <даты> [, <фильтр> ][, <конец года> ] )
CLOSINGBALANCEQUARTER ( <выражение>, <даты> [, <фильтр> ] )
CLOSINGBALANCEMONTH ( <выражение>, <даты> [, <фильтр> ] )
выражение | выражение, рассчитывающее скалярное значение |
даты | столбец дат (или выражение, его определяющее) |
фильтр | (необязательно) выражение, задающее фильтр |
конец года | (необязательно) дата окончания года, вводится в кавычках, по умолчанию 31 декабря |
Обратите внимание: для функций группы CLOSINGBALANCE нужно, чтобы были заполнены данные на последнее число периода.
Пример: найти сумму значений на последнюю дату месяца.
сумма к.п. = CLOSINGBALANCEMONTH ( SUM ( ' данные' [значение] ), ' Календарь' [Date] ) |
Следующие формулы в статье используются в построении отчетов не так часто, как те, о которых написано выше. Их понимание необходимо для создания развернутой аналитики и оформления, а также для более полного погружения в язык DAX.
11. VALUES
Функция VALUES возвращает значения столбца или таблицы:
- если указать столбец, то VALUES вернет список его уникальных значений;
- если как аргумент указать таблицу, то формула вернет список всех строк таблицы с сохранением повторений.
При вычислениях с VALUES примененные фильтры сохраняются и возвращаются видимые в текущем контексте значения, а пустые строки учитываются в расчетах. Обычно VALUES используется как промежуточная формула внутри других вычислений. Записывается формула просто:
VALUES ( <таблица или столбец> )
таблица или столбец | cтолбец, из которого должны возвращаться уникальные значения, или таблица, из которой должны возвращаться строки |
Пример: посчитать, сколько менеджеров работало в каждом городе.
менеджеры = COUNTROWS ( VALUES ( ' данные' [менеджер] ) ) |
Для сравнения в таблицу добавлен столбец с похожими вычислениями с помощью формулы ALL:
менеджеры = COUNTROWS ( ALL ( ' данные' [менеджер] ) ) |
Формула ALL игнорирует фильтры, поэтому в строках одинаковые цифры. Формула VALUES сохраняет фильтры и выдает правильный результат.
12. KEEPFILTERS
KEEPFILTERS – это еще одна полезная формула, которую используют вместе с CALCULATE и CALCULATETABLE. При вычислениях формула CALCULATE перезаписывает существующие фильтры в столбцах отчета. А если перезапись фильтров не требуется, и к действующим нужно просто добавить новые фильтры, это можно сделать с помощью KEEPFILTERS. Формула записывается так:
KEEPFILTERS ( <выражение> )
выражение | любое выражение |
Пример: посчитать выручку по определенной группе товаров.
Если записать формулу для вычисления так:
выручка = CALCULATE ( [выручка], ' спТовары' [товар] = " коньки" ) |
то сумма по выбранной группе запишется в каждой строке таблицы. KEEPFILTERS меняет поведение стандартное поведение формулы CALCULATE и сохраняет уже примененные фильтры – выручка по товару будет заполнена только в нужной строке:
выручка = CALCULATE ( [выручка], KEEPFILTERS ( ' спТовары' [товар] = " коньки" ) ) |
13. REMOVEFILTERS
REMOVEFILTERS очищает фильтры в указанной таблице или столбце.
Синтаксис формулы:
REMOVEFILTERS ( [ <таблица> или <столбец>[, <столбец>[, …] ] ] )
таблица | таблица, в которой нужно очистить фильтры |
столбец | столбец, в котором нужно очистить фильтры |
С помощью этой функции нельзя получить «промежуточную» таблицу, а можно только убрать фильтры. Формула REMOVEFILTERS применяется в вычислениях с CALCULATE. Например, с ее помощью можно посчитать долю (%) продаж товара от общей суммы продаж, как в примере с ALL.
Пример: найти долю от продаж товара по отношению к общей сумме выручки.
Доля, % = DIVIDE ( [выручка], CALCULATE ( [выручка], REMOVEFILTERS ( ' товары' ) ) |
Что лучше использовать для вычислений в этом примере – ALL или REMOVEFILTERS? Если в приложении доступна формула REMOVEFILTERS, и в формуле вам просто нужно очистить фильтры, то рекомендуется использовать REMOVEFILTERS.
14. ISFILTERED
Формула ISFILTERED определяет, применен ли прямой фильтр к столбцу. Если такой фильтр есть, то функция возвращает TRUE. С помощью этой формулы можно, например, управлять отображением итогов.
Синтаксис формулы:
ISFILTERED ( <столбец> )
столбец | имя столбца (не может быть выражением) |
Пример: посчитать среднюю цену по товарам, а по группам цену не показывать.
ср цена = IF ( ISFILTERED ( ' спТовары' [товар] ),DIVIDE( [выручка], [количество] ), BLANK() ) |
15. SELECTEDVALUE
Функция SELECTEDVALUE возвращает отфильтрованное значение. Если столбец отфильтрован по одному отдельному значению, то его можно получить с помощью SELECTEDVALUE, в противном случае функция возвращает «альтернативный результат».
С помощью этой формулы можно создавать комментарии или делать вычисления, которые зависят от выделенного (отфильтрованного) значения.
Записывается формула просто:
SELECTEDVALUE ( <столбец> [, <альтернативный результат> ] )
столбец | имя столбца (не может быть выражением) |
альтернативный результат | (необязательно) значение, если столбец не отфильтрован по одному значению, по умолчанию BLANK() |
Пример: заголовок для одного выбранного года.
Заголовок = " Продажи за " & SELECTEDVALUE (' Календарь' [год] ) |
Более интересные комментарии и заголовки можно создавать с помощью сочетаний SELECTEDVALUE и HASONEVALUE.
16. HASONEVALUE
HASONEVALUE проверяет, содержит ли контекст столбца только одно значение (или контекст должен быть отфильтрован до одного значения). Если это верно, то функция возвращает значение TRUE.
Эту функцию удобно применять, если вам нужно сделать расчет только по единичным значениям. А также для написания комментариев и пояснений.
Синтаксис формулы:
HASONEVALUE ( <столбец> )
столбец | имя столбца (не может быть выражением) |
Пример. Если выбран один город, в комментарии написать его название и сумму продаж. Если не выбран один город, то сумму по городам.
комментарий = IF ( HASONEVALUE ( ' спГород' [город] )," Продажи " & SELECTEDVALUE ( ' спГород' [город] ) & " : " & FORMAT ( SUM ( ' данные' [выручка] ), " #,##0" )," Продажи: " & FORMAT ( SUM ( ' данные' [выручка] ), " #,##0" ) ) |
18. USERELATIONSHIP
USERELATIONSHIP активирует связь в вычислении в соответствии существующей связью между двумя столбцами. Сама по себе отдельно эта функция не используется, ее можно применять в формулах CALCULATE, TOTALYTD и др. Функция USERELATIONSHIP, как и RELATED, относится к функциям связи. Но в отличие от RELATED, для USERELATIONSHIP вовсе не требуется, чтобы существующие связи между таблицами были активными.
Записывается формула просто:
USERELATIONSHIP ( <столбец1>, <столбец2> )
столбец1 | имя столбца, который представляет собой одну сторону связи (не может быть выражением) |
столбец2 | имя столбца с другой стороны связи (не может быть выражением) |
Эта формула отлично работает, если нужно показать несколько дат из разных столбцов на одной оси. Или при создании визуализаций по данным анкет. Пример, как создать вычисления с USERELATIONSHIP, смотрите в видео.
19. ADDCOLUMNS
ADDCOLUMNS добавляет вычисляемые столбцы к таблице. На выходе рассчитывается таблица с исходными и добавленными столбцами.
Синтаксис формулы:
ADDCOLUMNS ( <таблица>, <столбец>, <выражение>[, <столбец2>, < выражение2>]…)
таблица | название таблицы (или выражение, возвращающее таблицу) |
" новый столбец" |
название нового столбца, который вы добавляете к таблице. Вводится в двойных кавычках |
выражение | формула, которая используется для расчета значений столбца |
С помощью формулы ADDCOLUMNS можно создавать вычисления на основе существующих таблиц, например, сумму показателей:
SUMX ( ADDCOLUMNS ( ' данные' , " показатель" , <выражение>), [показатель]) |
Здесь функция ADDCOLUMNS создает новый столбец в таблице с названием «показатель», а SUMX суммирует его значения.
20. SUMMARIZE
SUMMARIZE – одна из очень часто используемых DAX-формул. Она возвращает таблицу для запрошенных значений, объединенных в наборы групп. Обычно в расчетах эта формула используется для создания комбинации значений вместо таблицы со всеми данными.
Синтаксис формулы:
SUMMARIZE ( <таблица>, <столбец группировки> [, <столбец группировки> ] … [, <имя>, <выражение> ] … )
таблица | выражение DAX, возвращающее таблицу |
столбец группировки | имя столбца, используемого для создания групп на основе значений, найденных в нем (не может быть выражением) |
имя | имя для сводного столбца в двойных кавычках |
выражение | выражение DAX, возвращающее одно скалярное значение, которое может вычисляться несколько раз (для каждой строки или контекста) |
Если в «промежуточную» таблицу добавляется новый столбец, то это рекомендуется делать с помощью ADDCOLUMNS: сначала создается сгруппированная таблица с помощью SUMMARIZE, а затем в нее добавляется столбец в ADDCOLUMNS.