Вычисления в контексте строки и особенности работы CALCULATE

Всем привет!

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

Если Вы заглянули на эту страницу, то наверняка знаете, что функция CALCULATE позволяет изменить контекст расчёта при помощи фильтров, а при использовании в контексте строки — выполняет преобразование контекста строки в контекст фильтра. Базовые сценарии использования CALCULATE достаточно просты для освоения и понимания, а вот понять и принять то, как работает преобразование контекста уже сложнее. Давайте разбираться.

Рис. 1. Пример данных

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

% от продаж продавца =
DIVIDE (
    SUMX ( ‘Продажи’; [Кол-во] * [Стоимость за единицу] );
    CALCULATE (
        SUMX ( ‘Продажи’; [Кол-во] * [Стоимость за единицу] );
        ALLEXCEPT ( ‘Продажи’; ‘Продажи'[Продавец] )
    )
)

Делимое рассчитывается в контексте фильтра, определенном в визуальном элементе, а в делителе используется Calculate в комбинации с модификатором контекста ALLEXCEPT, примененным к таблице ‘Продажи’ и столбцу [Продавец], который указывает на необходимость игнорирования любых фильтров, примененных ко всем столбцам таблицы ‘Продажи’, кроме столбца [Продавец] в контексте фильтра.

Рис. 2. Пример меры

А теперь также, но в вычисляемом столбце

Теперь выполним точно такой же расчёт при помощи вычисляемого столбца. Для удобства в первую очередь добавим новый столбец: Итого = [Кол-во] * [Стоимость за единицу]

Рис. 3. Простой вычисляемый столбец

Как Вы видите, для расчета значений в каждой строке используются только данные, содержащиеся в ней самой. Как в динамических таблицах Excel. Если же Вы создадите вычисляемый столбец при помощи формулы Итог без Calculate = SUM ( ‘Продажи'[Итого] ), то увидите, что в каждой строке столбца [Итог без Calculate] содержится одинаковое значение, равное сумме всех значений в столбце [Итого]

Рис. 4. Вычисляемый столбец возвращает сумму всех значений исходного

Это происходит потому, что в контексте строки не существует никакой информации о том, какие фильтры должны быть применены к таблице ‘Продажи’, в результате на вход функция SUM получает все значения из столбца ‘Итого’ и суммирует их. Как только я добавлю в формулу вычисляемого столбца функцию CALCULATE, его поведение изменится.

Рис. 5. Преобразование контекста при помощи CALCULATE

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

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

ПродавецФруктКол-воСтоимость за единицуИтогоИтог без Calculate
Продавец 2Апельсин10505004 245

После применения такого набора фильтров к таблице «Продажи» в ней останется только одна строка со значением в столбце итого — 500. Соответственно, значение в столбце [Итог с Calculate] также будет равно 500.

Рис. 6. Преобразование контекста — пример 1.

А вот в строках, выделенных красным мы видим уже результат, отличающийся от других строк. Несмотря на то, что значение в столбце [Итого] в каждой из них равно 250, столбец  [Итог с Calculate] содержит результат 500, что наглядно иллюстрирует то, как работает преобразование контекста. Для каждой из двух строк формируется одинаковый набор фильтров:

ПродавецФруктКол-воСтоимость за единицуИтогоИтог без Calculate
Продавец 1Апельсин5502504 245

В результате применения такого набора фильтров к таблице ‘Продажи’ в ней останутся 2 строки со значениями в столбце [Итого]=250. 250+250 = 500, что и отображается в новом вычисляемом столбце

Рис. 7. Преобразование контекста — пример 2.

На следующем шаге рассчитаем сумму продаж по каждому продавцу. Кажется, что всё достаточно просто и нужно всего лишь добавить функцию FILTER: Продажи каждого продавца =
CALCULATE (
    SUM ( ‘Продажи'[Итого] );
    FILTER ( ‘Продажи’; ‘Продажи'[Продавец] = ‘Продажи'[Продавец] )
)
Но не тут то было — столбец  опять содержит сумму всех строк в таблице ‘Продажи’.

Рис. 8. Calculate выполняет преобразование контекста только для первого аргумента

Давайте разберемся, каким образом работает функция CALCULATE в вычисляемых столбцах.

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

Порядок расчета следующий:

  1. Производится расчет Filter в текущем контексте фильтра.
  2. Выполняется преобразование контекста строки в контекст фильтра для первого аргумента.
  3. Производится применение Filter, рассчитанных на первом шаге.
  4. Производится расчет первого аргумента CALCULATE

Например, для первой строки по сути формируется следующее выражение (в целях упрощения я пропускаю все вычисляемые столбцы):

VAR _F =
    FILTER ( ‘Продажи’; ‘Продажи'[Продавец] = ‘Продажи'[Продавец] )
VAR _CT =
    FILTER (
        ‘Продажи’;
        ‘Продажи'[Продавец] = «Продавец 1»
            && ‘Продажи'[Фрукт] = «Апельсин»
            && ‘Продажи'[Кол-во] = 5
            && ‘Продажи'[Стоимость за единицу] = 50
    )
RETURN
    CALCULATE ( CALCULATE ( SUM ( ‘Продажи'[Итого] )_F )_CT )

Поскольку внутренний CALCULATE рассчитывается после внешнего, то он имеет приоритет и дополняет или переопределяет модификаторы контекста, определенные во внешних CALCULATE. В данном случае, фильтр _F возвращает таблицу ‘Продажи’ (все столбцы) целиком, т.е. допускает больший набор значений, чем фильтр _СT, сформированный в результате переноса контекста и перезаписывает его.

Как же получить корректный результат? Есть два варианта — использовать переменные или функцию EARLIER.

Вариант с использованием Var:

Продажи каждого продавца =
VAR _salesman = ‘Продажи'[Продавец]
RETURN
    CALCULATE (
        SUM ( ‘Продажи'[Итого] );
        FILTER ( ‘Продажи’; ‘Продажи'[Продавец] = _salesman )
    )

В этом случае значение переменной _salesman рассчитывается в контексте строки, для каждой строки получает значение из столбца [Продавец], запоминает его и передаёт в Filter в виде скалярного значения.

В итоге для первой строки таблицы по сути формируется следующее метавыражение:

VAR _F =
    FILTER ( ‘Продажи’; ‘Продажи'[Продавец] = «Продавец 1» )
VAR _CT =
    FILTER (
        ‘Продажи’;
        ‘Продажи'[Продавец] = «Продавец 1»
            && ‘Продажи'[Фрукт] = «Апельсин»
            && ‘Продажи'[Кол-во] = 5
            && ‘Продажи'[Стоимость за единицу] = 50
    )
RETURN
    CALCULATE ( CALCULATE ( SUM ( ‘Продажи'[Итого] )_F )_CT )

Поскольку фильтр _F также содержит все столбцы таблицы, то он перезаписывает фильтр, сформированный в результате переноса контекста (_CT).

Устаревший и менее производительный вариант, который мы обречены использовать в PowerPivot — функция EARLIER.

Рис. 9. Функция EARLIER

Продажи Earlier =
CALCULATE (
    SUM ( ‘Продажи'[Итого] );
    FILTER ( ‘Продажи’; ‘Продажи'[Продавец] = EARLIER ( ‘Продажи'[Продавец] ) )
)

Функция EARLIER в данном случае получает значение в столбце Продавец в контексте строки отдельно от остальных частей формулы. В реальности все еще сложнее, поскольку у функции EARLIER есть необязательный параметр «Уровень». Если мы не указываем значение уровня, то используется значение 1. Что это значит для нас?

Выражение =  EARLIER ( ‘Продажи'[Продавец];1) или EARLIER ( ‘Продажи'[Продавец] ) рассчитывается во внешнем, относительно определенного функцией FILTER, контексте строки – т.е. в контексте строки основной таблицы ‘Продажи’, поэтому для первой строки возвращает значение «Продавец 1» и т.д. Если Вы можете использовать переменные в DAX коде, то настоятельно рекомендую использовать их вместо функции EARLIER.

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

А еще — полезные материалы есть здесь.


Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

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