Глава 8. DAX-функция CALCULATE()

Это продолжение перевода книги Роб Колли. Формулы DAX для Power Pivot. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава       Содержание    Следующая глава

DAX-функция CALCULATE() близка функциям Excel СУММЕСЛИ() и СУММЕСЛИМН(). Последние суммируют значения в указанном столбце, но отфильтровывают строки, которые соответствуют критериям в том же или ином столбце. Так, например, можно использовать СУММЕСЛИ() для суммирования по столбцу Sales, но только для строк, где столбец Year содержит 2012. Так вот, DAX-функция CALCULATE() еще мощнее:

1. Ее синтаксис проще.
2. Агрегирование возможно для гораздо более широкого круга вычислений. Например, в Excel нет функции МАКСЕСЛИ (начиная с Excel 2016 такая функция есть 🙂 ).
3. Ее можно использовать в сводных таблицах (как часть формулы меры), чего не может сделать обычная СУММЕСЛИ().

Рис. 8.1. Синтаксис функции CALCULATE()

Скачать заметку в формате Word или pdf

Синтаксис функции CALCULATE()

Например, CALCULATE(SUM(Sales[Margin]); Sales[Year]=2001); CALCULATE([Sales per Day]; Sales[Year]=2002; Sales[ProductKey]=313).

Посмотрим на CALCULATE() в действии. Возьмем простую сводную таблицу из файла ch08_CALCULATE…

Рис. 8.2. Продажи по годам

… и добавим новую меру [2002 Sales] = CALCULATE([Total Sales]; Sales[Year]=2002)

Обратите внимание: мы использовали имя меры в качестве первого аргумента функции. Аргумент [Filter1] не заключен в кавычки – столбец Year числовой (дата в Excel – это число). Если бы он был текстовым, нам пришлось бы использовать кавычки «2002». Мы использовали только один фильтр, но могли бы использовать несколько.

Рис. 8.3. Новая мера показывает продажи только за 2002 год

Эти результаты вас удивляют? Возможно, вы ожидали, что в 2001, 2003 и 2004 годах будут отображаться нули для новой меры!?

Давайте заменим годы на месяцы:

Рис. 8.4. Продажи по месяцам: новая мера ведет себя более предсказуемо

Как работает функция CALCULATE()

Есть три ключевых момента, которые нужно знать об аргументах [Filter]:

1. Аргументы [Filter] работают во время шага 2 третьего золотого правила мер DAX. Фильтры функции CALCULATE() изменяют набор фильтров, для которых будет вычисляться мера.
2. Если аргумент [Filter] действует на столбец, который уже есть в сводной таблице, он переопределяет набор фильтров для этого столбца. В нашем примере (см. рис. 8.3) в ячейке D3 используется фильтр [Year]=2001, но второй аргумент функции CALCULATE() уже содержит фильтр Sales[Year]=2002. Поэтому установка сводной таблицы на 2001 год отменяется и переопределяется внутри функции CALCULATE() для меры [2002 Sales]. Вот почему ячейки за 2001, 2003, 2004 гг. и итоги на рис. 8.3 вернули продажи за 2002 год.
3. Если аргумент [Filter] действует на столбец, которого нет в сводной таблице (как на рис. 8.4), он просто добавляется в набор фильтров.

Таким образом, шаг 2 третьего золотого правила мер DAX звучит так: Измените набор фильтров, если используете функцию CALCULATE().

Пример использования функции CALCULATE()

В бизнесе не все транзакции являются продажами. Некоторые компании выделяют: обычные продажи, возврат и рекламные образцы.

Откройте Excel-файл ch08_CALCULATE.xlsx, перейдите на вкладку Power Pivot, кликните на кнопку Управление. В окне Power Pivot перейдите на вкладку Sales. Вы найдете столбец TransType. Он имеет три значения:

Рис. 8.5. Три типа трансакций в базе данных Power Pivot

Определим четыре новые меры. Обычные продажи – только операции типа 1:

[Regular Sales] = CALCULATE([Total Sales]; Sales[TransType]=1)

Рекламные продажи – сделки типа 3:

[Promotional Sales] = CALCULATE([Total Sales]; Sales[TransType]=3)

Возврат – операции типа 2, выраженные отрицательным числом:

[Refunds] = CALCULATE([Total Sales]; Sales[TransType]=2)*-1

Чистые продажи:

[Net Sales] = [Regular Sales] + [Promotional Sales] + [Refunds]

А также давайте рассчитаем долю рекламных продаж:

[Pct Sales on Promo] = [Promotional Sales]/([Regular Sales] + [Promotional Sales])

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

Еще один пример

Во втором примере мы собираемся определить несколько мер, которые показывают активность клиентов. Для начала – базовая мера, показывающая, сколько клиентов было активно в данный момент времени:

[Active Customers] = DISTINCTCOUNT(Sales[CustomerKey])

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

Далее – количество клиентов в 2001 г. (первый год в бизнесе):

[2001 Customers] = CALCULATE([Active Customers]; Sales[Year]=2001)

Мера роста по сравнению с 2001 годом:

[Customer Growth Since 2001] =DIVIDE([Active Customers]-[2001 Customers]; [2001 Customers])

Функция DIVIDE() – безопасное деление: позволяет указать альтернативное значение, если знаменатель равен нулю.

Рис. 8.7. Меры активности клиентов

В аргументе [Filter] помимо оператора = можно использовать: <, >, >=, <=, <>.

Если в одной функции CALCULATE() несколько аргументов [Filter], все они подчиняются логическому И, т.е., отбираемая строка должна соответствовать каждому аргументу [Filter]. Если вам нужно применить логическое ИЛИ, вы можете использовать оператор ||. Например, общий объем продаж (но не возвратов):

=CALCULATE([Total Sales]; Sales[TransType]=1||Sales[TransType]=3)

Важно! При использовании в одном аргументе [Filter] оператор || можно применять для данных только одного столбца.

Обратите внимание, что общее количество активных клиентов в ячейке С7 на рис. 8.7 не равно сумме по ячейкам С3:С6. Это еще один прекрасный пример того, почему важно думать о мерах, агрегирующих данные по исходной таблице (таблицам) в Power Pivot, а не по значениям в самой сводной таблице Excel. Что касается ячейки итогов, то о ней следует думать, как об агрегировании в ситуации фильтра Все. В контексте этой ячейки поле Year отсутствует. Это легко проверить, если удалить Year в сводной таблице из области Строки:

Рис. 8.8. Только общие итоги

Когда мы очищаем фильтр Year, формула DISTINCTCOUNT(Sales[CustomerKey]) работает с нефильтрованной таблицей и подсчитывает каждого клиента только один раз! Мы получаем 18 484, что является правильным ответом.

Не все итоги являются общими, но принцип сохраняется:

Рис. 8.9. Фильтры на различных итоговых ячейках

Физическое расположение ячейки с мерой внутри сводной таблицы не имеет значения. Важен только набор ее фильтров. В нашем примере набор фильтров Year=2002, Month=ALL абсолютно одинаков для механизма DAX независимо от того, где расположены поля Year и MonthNum в строках, столбцах, фильтрах отчета или срезах.