Как пользоваться CALCULATE в Power BI

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

Одна из основных функций этого языка — CALCULATE. Она позволяет изменить условия, при которых выполняется расчет. Например, с ее помощью можно:

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

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

Что такое мера и контекст фильтра 

В DAX результат расчета зависит от самой формулы и от того, какие данные участвуют в расчете в данный момент. Чтобы понять, как это работает, сначала разберемся с понятием меры. В примерах будем использовать Microsoft Power BI Desktop (версия 2.157.879.0 64-bit).

Как мы знаем, в Power BI данные обычно хранятся в таблицах. Например, в таблице Sales могут быть сведения о продажах:

Чтобы сделать расчет на основании этих данных, можно создать меру. 

Мера — это формула на языке DAX, которая описывает, как Power BI должен вычислить определенное значение.

Например, она может:

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

Создадим меру для расчета общей суммы продаж.

1. В области «Данные» нажмите ПКМ на название созданной таблицы и выберите «Создать меру» в выпадающем списке.

2. В строке ввода формулы введите формулу расчета общей суммы продаж:

Total Sales = SUM(Sales[Amount])

Здесь:

• Total Sales — название меры,
• Sales — таблица, из которой берутся данные, 
• Amount — столбец с суммами продаж, 
• SUM — агрегатная функция DAX, которая складывает значения этого столбца.

После ввода формулы нажмите Enter. В области «Данные» внутри таблицы Sales появится новое поле Total Sales со значком калькулятора.

При этом мера не добавляет еще один столбец в исходную таблицу. В ней хранится правило расчета: взять значения из столбца Amount, которые участвуют в текущем расчете, и сложить их.

Чтобы применить данное правило, меру нужно добавить в один из визуальных элементов Power BI, например, в карточку.

Как вывести результат меры в карточку

Карточка — это визуальный элемент Power BI, который показывает один показатель крупным числом. Чтобы вывести меру, которую мы ранее создали:

1. Перейдите на страницу отчета. 

2. Справа откройте область «Визуализации».

3. Выберите визуальный элемент «Карточка».

4. На странице появится пустая карточка.

5. В области «Данные» найдите меру Total Sales и перетащите ее на карточку или добавьте ее в поле данных выбранной карточки.

6. После добавления меры Power BI выполнит записанный в ней расчет и выведет результат в карточке.

В таблице Sales сейчас три значения Amount:

80 000 + 50 000 + 120 000 = 250 000

Поэтому на карточке появится число 250 000.

Как мера связана с контекстом

Сейчас мера Total Sales находится в карточке и показывает общую сумму продаж — 250 000. Это происходит потому, что для расчета используются все строки таблицы Sales.

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

1. Например, добавим в отчет визуальный элемент «Таблица»:

1. Поместим в него поле City и меру Total Sales. Получим таблицу:

Формула меры не изменилась, но Power BI вычислил ее отдельно для каждой строки визуальной таблицы:

• Для строки «Москва» действует условие City = Москва.
• Для строки «Новосибирск» действует условие City = Новосибирск.
• Для строки «Всего» ограничения по городу нет.

То есть перед каждым вычислением меры Power BI определяет, какие данные сейчас разрешено использовать в расчете.

Контекст фильтра — это условия, которые определяют, какие данные Power BI учитывает при текущем расчете меры.

Как работает CALCULATE с контекстом

Функция CALCULATE позволяет изменить контекст прямо в формуле.

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

Total Sales = SUM(Sales[Amount])

Теперь создадим еще одну меру и укажем, что нас интересуют только продажи ноутбуков:

Laptop Sales =
CALCULATE(
    [Total Sales],
    Sales[Category] = "Ноутбуки"
)

Здесь:

• Total Sales — мера, которую нужно вычислить.
• Sales[Category] = «Ноутбуки» — условие, которое изменяет контекст фильтра: Power BI должен учитывать только строки, где в столбце Category указано значение «Ноутбуки». 

Появится новое поле меры Laptop Sales:

В исходной таблице этому условию соответствуют две строки:

Поэтому мера Laptop Sales вернет:

80 000 + 120 000 = 200 000

CALCULATE работает по такому алгоритму:

1. Получает выражение существующего контекста.
2. Изменяет контекст фильтра согласно новому условию. 
3. Вычисляет выражение уже в измененном контексте.

Синтаксис функции CALCULATE выглядит примерно так:

CALCULATE(<выражение>, <фильтр1>, <фильтр2>, ...)

Здесь:

• <выражение> — то, что нужно вычислить, например мера,
• <фильтр1>, <фильтр2> и далее — условия, которые изменяют контекст фильтра перед вычислением.

Если добавить меру Laptop Sales в ту же таблицу отчета, получится:

Для строк городов уже действует контекст фильтрации по городу. CALCULATE добавляет к нему условие Category = Ноутбуки, поэтому в расчет попадает только продажа ноутбуков в указанном городе.

Что происходит с уже действующим контекстом

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

• в строке «Москва» мера Total Sales рассчитывается при условии City = Москва, 
• в строке «Новосибирск» при условии City = Новосибирск. 

Поэтому Total Sales показывает соответственно 130 000 и 120 000.

Создадим другую меру:

Moscow Sales =
CALCULATE(
    [Total Sales],
    Sales[City] = "Москва"
)

Здесь CALCULATE явно задает для расчета условие:

City = Москва

Можно добавить эту меру в уже существующую визуальную таблицу. В ней появится новый столбец Moscow Sales, а поле City, другие меры и их результаты останутся прежними.

Для строки «Москва» визуальная таблица уже задает фильтр:

City = Москва

Как и CALCULATE. Поэтому результат ожидаемо равен 130 000.

А вот для строки «Новосибирск» визуальная таблица изначально задает:

City = Новосибирск

Но внутри меры Moscow Sales функция CALCULATE задает для того же столбца City условие City = Москва.

Так что при вычислении Moscow Sales функция CALCULATE временно перезаписывает фильтр по столбцу City. При этом остальной контекст и сама строка визуальной таблицы никуда не исчезают.

Поэтому Moscow Sales возвращает 130 000, хотя сама строка таблицы называется «Новосибирск».

CALCULATE не меняет строку «Новосибирск», не удаляет ее и не изменяет фильтры для Total Sales или Laptop Sales. Она меняет контекст только на время вычисления меры Moscow Sales.

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

Однако, если не нужно перезаписывать существующий фильтр, можно использовать функцию KEEPFILTERS. Тогда Power BI сохранит существующий фильтр и применит к нему новое условие, то есть будет учитывать пересечение двух фильтров. Подробнее об этой функции расскажем позже.

Как использовать несколько фильтров в CALCULATE

Создадим новую таблицу SalesExample:

Создадим для нее меру:

Example Sales = SUM(SalesExample[Amount])

В CALCULATE можно задать сразу несколько фильтров. Например, посчитаем продажи ноутбуков только в Москве:

Moscow Laptop Sales =
CALCULATE(
    [Example Sales],
    SalesExample[City] = "Москва",
    SalesExample[Category] = "Ноутбуки"
)

Здесь:

• SalesExample[City] = «Москва» — оставить продажи для Москвы,
• SalesExample[Category] = «Ноутбуки» — оставить продажи ноутбуков.

Если перечислить фильтры в CALCULATE через запятую, для расчета должны выполняться все указанные условия. Поэтому мера Moscow Laptop Sales вернет 80 000.

Для объединения условий в DAX также используют логические операторы &&
и ||.

• && означает И, то есть должны выполняться оба условия. Например:

Moscow Laptop Sales AND =
CALCULATE(
    [Example Sales],
    SalesExample[City] = "Москва" &&
    SalesExample[Category] = "Ноутбуки"
)

Здесь одновременно должны выполняться два условия: город — Москва, категория — ноутбуки. Получим:

В этом случае && дает тот же результат, что и перечисление фильтров через запятую. Оператор будет полезнее, когда несколько условий нужно объединить в одном логическом выражении, например вместе с ||.

|| означает ИЛИ, то есть достаточно выполнения хотя бы одного из условий. Например:

Selected Sales OR =
CALCULATE(
    [Example Sales],
    SalesExample[City] = "Новосибирск" ||
    SalesExample[Category] = "Смартфоны"
)

Согласно такой логике, в расчет попадут:

Получим:

50 000 + 120 000 + 70 000 = 240 000

Когда внутри CALCULATE нужен FILTER

Функция FILTER получает таблицу, проверяет ее строки по заданному условию и возвращает новую таблицу с теми строками, которые этому условию соответствуют.

Для простых условий FILTER обычно не используют, так как их можно указать прямо внутри CALCULATE. Но он полезен для более сложных сравнений. 

Например, мы хотим посчитать продажи только тех категорий, общая сумма продаж которых превышает 100 000.

В таблице SalesExample продажи по категориям составляют:

Нам нужно оставить категории «Ноутбуки» и «Смартфоны», а «Планшеты» исключить.

Для этого в мере можно использовать FILTER:

High Category Sales =
CALCULATE(
    [Example Sales],
    FILTER(
        VALUES(SalesExample[Category]),
        [Example Sales] > 100000
    )
)

Здесь VALUES создает таблицу с уникальными значениями столбца Category:

Ноутбуки, Смартфоны, Планшеты.

Затем FILTER поочередно проверяет их и оставляет только те, для которых мера [Example Sales] превышает 100 000.

В нашем случае останутся:

• Ноутбуки — 200 000,
• Смартфоны — 120 000.

CALCULATE рассчитывает [Example Sales] уже только для этих категорий. Поэтому High Category Sales вернет:

200 000 + 120 000 = 320 000

Как убрать фильтр в CALCULATE

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

Например, выведем в визуальную таблицу поле Category и меру Example Sales.

Для каждой строки действует свой контекст фильтра. Например, в строке «Ноутбуки» мера Example Sales рассчитывается только по продажам ноутбуков и возвращает 200 000.

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

All Sales =
CALCULATE(
    [Example Sales],
    REMOVEFILTERS(SalesExample[Category])
)

REMOVEFILTERS убирает фильтр со столбца Category на время этого расчета. При этом другие действующие фильтры сохраняются. Поэтому в каждой строке All Sales будет учитывать продажи всех категорий и возвращать 390 000:

Как посчитать долю от общей суммы

REMOVEFILTERS можно использовать для расчета доли каждой категории в общей сумме продаж.

Создадим меру:

Sales Share =
DIVIDE(
    [Example Sales],
    CALCULATE(
        [Example Sales],
        REMOVEFILTERS(SalesExample[Category])
    )
)

Функция DIVIDE делит первое выражение на второе.

Для строки «Ноутбуки»:

• [Example Sales] возвращает продажи ноутбуков — 200 000, 
• REMOVEFILTERS снимает ограничение по категории, поэтому знаменатель равен общей сумме продаж — 390 000.

Получим:

0,51.

Для других категорий:

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

Чем REMOVEFILTERS отличается от ALL

Для снятия фильтров также можно использовать ALL. Но возможности этой функции шире. Например, ALL(Table) может возвращать все строки таблицы, а ALL(Column) — все значения указанного столбца, игнорируя действующие фильтры. 

Поэтому ALL можно использовать не только как модификатор фильтра, но и как табличная функция. 

REMOVEFILTERS, напротив, предназначена именно для снятия фильтров и сама таблицу не возвращает. 

Если задача состоит только в удалении фильтра, Microsoft рекомендует использовать REMOVEFILTERS, если эта функция поддерживается используемым инструментом.

Как сохранить существующий фильтр с помощью KEEPFILTERS

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

Функция KEEPFILTERS позволяет изменить это поведение. Она сохраняет действующий фильтр и применяет к нему новое условие. В результате Power BI учитывает пересечение двух фильтров.

Рассмотрим пример на таблице SalesExample. Создадим меру с KEEPFILTERS:

Selected Categories Keep =
CALCULATE(
    [Example Sales],
    KEEPFILTERS(
        SalesExample[Category] IN {"Ноутбуки", "Смартфоны"}
    )
)

Здесь оператор IN означает, что в расчет нужно включить только категории «Ноутбуки» и «Смартфоны».

Power BI не будет перезаписывать фильтр текущей строки, а найдет пересечение между ним и условием внутри KEEPFILTERS.

Для строки «Ноутбуки»:

• внешний фильтр: Category = Ноутбуки,
• новый фильтр: Category IN {Ноутбуки, Смартфоны},
• пересечение: Ноутбуки.

Получим: 200 000.

Для строки «Смартфоны» пересечением будет Смартфоны, поэтому результат — 120 000.

Для строки «Планшеты» пересечения нет, потому что планшеты не входят в набор {«Ноутбуки», «Смартфоны»}. Поэтому мера вернет пустое значение.

В строке «Всего» фильтра по отдельной категории нет. Поэтому KEEPFILTERS просто применяет условие Category IN {«Ноутбуки», «Смартфоны»} ко всей таблице и возвращает сумму продаж этих двух категорий:

200 000 + 120 000 = 320 000

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

Итоги

• CALCULATE изменяет контекст фильтра (условия отбора данных для расчета) и затем вычисляет результат. 
• Контекст фильтра определяет, какие данные Power BI учитывает при расчете меры. 
• В CALCULATE можно задать один или несколько фильтров. Если они перечислены через запятую, должны выполняться все указанные условия.
• Если новый фильтр относится к столбцу, который уже отфильтрован, CALCULATE перезаписывает существующий фильтр по этому столбцу на время вычисления выражения.
• Для более сложного отбора данных вместе с CALCULATE можно использовать FILTER. 
• REMOVEFILTERS позволяет убрать определенный фильтр из расчета, например чтобы получить общую сумму или рассчитать долю. 
• KEEPFILTERS сохраняет существующий фильтр и применяет к нему новое условие, поэтому учитывается пересечение фильтров. 
• Для объединения логических условий в DAX используют && (И) и || (ИЛИ).