usto-excel

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

1. Более понятный синтаксис;
2. Он не ограничивается лишь подсчётом суммы по условию. К примеру в Excel, СУММЕСЛИМН() используется для подсчёта суммы по условию, СЧЕТЕСЛИМН() для подсчёта количества по условию а СРЗНАЧЕСЛИМН() для подсчёта среднего значения. Однако в Excel нету функций МАКСЕСЛИМН(), МИНЕСЛИМН() или же СТДОТКЛЕСЛИМН(). В этом смысле CALCULATE() безграничен. Он позволяет использовать любую функцию агрегирования (либо комплексную формулу) и рассчитывать её по указанным условиям;
3. Он используется для создания мер а СУММЕСЛИМН() не может быть использован в сводных таблицах.

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

CALCULATE(<логическое выражение>,<фильтр 1>,<фильтр 2>,...) 
пример: CALCULATE(SUM(t_sales[Маржа]), t_sales[Год]=2001)
пример: CALCULATE([ПродажиЗаДень], t_sales[Год]=2002, t_sales[КодПродукта]=313)

Конечно же в фильтр-аргументах наряду с оператором "=" можно также использовать:

• < (меньше чем);
• > (больше чем);
• <= (меньше или равно);
• >= (больше или равно);
• <> (не равно).

CALCULATE() в действии - несколько быстрых примеров

Начнём с простой сводной таблицы. Переместим "Год" в поле "Строки", а [ИтогоПродаж] в поле "Значения".



Теперь создадим меру, которая высчитывает сумму продаж за 2002 год:

[Продажи_2002]=CALCULATE([ИтогоПродаж], t_sales[Год]=2002)

Обратите внимание:

1. Мы использовали имя другой меры в качестве аргумента для CALCULATE(). То есть в качестве логического выражения в CALCULATE() может быть использована как формула так и уже существующая мера;
2. В первом фильтр-аргументе 2002 не в кавычках. Это потому, что формат столбца числовой. Если бы формат столбца был текстовым, тогда фильтр-аргумент был бы равен ="2002";
3. В этой мере был использован лишь один фильтр-аргумент, но при необходимости можно добавить ещё столько фильтр-аргументов, сколько захотим.

Как видите, значения [Продажи_2002] и [ИтогоПродаж] в строке 2002 совпадают. Однако уверен что Вы недоумеваете почему в строках 2001, 2003, 2004 вместо нулей также отображается сумма продаж за 2002 год. Объясню чуть позже. А пока давайте заменим в поле "Строки" сводной таблицы "Год" на "НомерМесяца".


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

1. Фильтр-аргументы функции CALCULATE() действуют в той фазе вычисления меры в которой применяются фильтры. Эти аргументы изменяют фильтр-контекст сводной таблицы;
2. Если фильтр-аргументы применяются к столбцу который уже находится в сводной таблице, то они изменяют контекст сводной для этого столбца. Именно поэтому в первом приведённом примере, функция CALCULATE() отображала одинаковое значение для всех строк столбца Год;
3. Если фильтр-аргумент применяется к столбцу, не находящемуся в сводной таблице, то он дополняет существующий фильтр-контекст сводной. Во втором примере, у нас имелась сводная таблица в которой указывалась разбивка продаж по номерам месяца. В этом примере фильтр-аргумент меры [Продажи_2002] дополнил существующий фильтр-контекст сводной и отображал продажи по каждому месяцу в 2002 году.

Чтобы закрепить наше понимание использования функции CALCULATE() используем два более полезных примера.

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

В нашей таблице продаж, в отдельном столбце "ТипТранзакции", указаны три типа транзакций, характерных для розничного бизнеса:

1. обычные продажи;
2. рекламные продажи;
3. возвраты

С помощью функции CALCULATE() мы можем создать меры для вычисления объёма продаж по каждому типу транзакции:

[ОбычныеПродажи]=CALCULATE([ИтогоПродаж],t_sales[ТипТранзакции]=1)
[РекламныеПродажи]=CALCULATE([ИтогоПродаж],t_sales[ТипТранзакции]=3)
[Возвраты]=CALCULATE([ИтогоПродаж],t_sales[ТипТранзакции]=2)*-1

*Так как Возвраты уменьшают сумму продаж, мы делаем их значение негативным чтобы они визуально отличались от обычных продаж.

И с помощью этих мер мы можем вычислить сумму чистых продаж:

[ЧистыеПродажи]=[ОбычныеПродажи]+[РекламныеПродажи]+[Возвраты]

Или же узнать какой процент общих продаж составляют РекламныеПродажи:

[ПроцРекламныхПродаж]=[РекламныеПродажи]/([ОбычныеПродажи]+[РекламныеПродажи]) 

Пример №2: рост с начала деятельности

Создадим базовую меру (мера не ссылающаяся на другие меры) рассчитывающую количество активных клиентов:

[АктивныеКлиенты]=DISTINCTCOUNT(t_sales[КодКлиента])

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

[Клиенты2001]=CALCULATE([АктивныеКлиенты],t_sales[Год]=2001)

Теперь, на основе этих двух мер мы можем рассчитать процент прироста клиентов по отношению к первому году начала продаж:

[ПриростКлиентовС2001]=DIVIDE([АктивныеКлиенты]-[Клиенты2001],[Клиенты2001])

Комбинирование фильтр-аргументов

Как Вы уже наверное поняли, функция CALCULATE() может принимать неограниченное количество фильтр-аргументов. Однако, по умолчанию все они применяются по принципу "И тот фильтр-аргумент И этот". Если же нужно, чтобы фильтр-аргументы применялись по принципу "ИЛИ" нужно использовать оператор "||".

  = CALCULATE([ИтогоПродаж],
 t_sales[ТипТранзакции]=1||t_sales[ТипТранзакции]=3)) 

И помните, при использовании оператора "||" возможно сравнивать лишь значения одного столбца - в нашем случае ТипТранзакции. Нельзя использовать оператор "||" для сравнения двух разных столбцов.