SUM против SUMX в DAX: почему для построчного расчета нужен итератор

1. Развилка: номер уже в столбце?

Перед объяснением один вопрос, на который стоит ответить по памяти. Подумайте о последней мере, которую вы написали. Скажем, были ли уже где-то сохранены суммированные значения или модель должна была сначала проработать каждое из них.

Этот вопрос определяет, какую функцию вы достигнете, и на него есть два ответа.

Ответ первый: число уже находится в столбце. Единицы, выручка, количество, численность персонала. Кто-то сохранил это значение, по одному значению в каждой строке, и сложить его — это вся работа. Если это правда, ты хочешь SUM, и вы передаете ему колонку.

Ответ второй: номера пока не существует. Линейный доход, когда в таблице указаны только единицы измерения и цена. Маржа, когда стоимость находится на другой таблице. Взвешенная оценка, расширенная сумма, что угодно, в середине которой есть оператор. Если это правда, что-то должно посещать каждую строку, вычислять там значение и только затем складывать их. Это что-то SUMX.

Решающее значение между ними имеет не то, насколько сложной выглядит формула. Вопрос в том, можете ли вы указать на столбец на панели «Поля» и сказать «этот». Если можете, это СУММА. Если вместо этого вам нужно описать вычисление, то это SUMX.

Это важно, потому что неправильный выбор не всегда приводит к ошибке. Иногда он выдает число, и следующие три раздела посвящены тому, что это за число и почему оно выживает.

2. Что SUM примет, а что нет

Справочная страница Майкрософт по SUM содержит один параметр и описывает его в шести словах: «Столбец, содержащий числа для суммирования». Не выражение. Колонка.

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

Revenue = SUM ( Orders[Units] * Orders[UnitPrice] )

Power BI отклоняет это сообщение, необычно прямое для механизма формул: Функция СУММ принимает в качестве аргумента только ссылку на столбец. В модели под названием ничего не хранится Units * UnitPrice, поэтому столбца для передачи нет.

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

Revenue = SUM ( Orders[Units] ) * SUM ( Orders[UnitPrice] )

Два столбца, две допустимые суммы, одно умножение. Power BI принимает его без жалоб, поскольку в нем нет ничего плохого, как в DAX. Это неправильно только как арифметика.

3. Неудача: 2630 значит 12995.

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

Правильный ответ 2,630, потому что 880 + 850 + 660 + 240 = 2630. Именно это и возвращает SUMX: он обрабатывает каждую строку, а затем складывает четыре результата.

Теперь версия, которая компилируется. SUM(Единицы) равна 4 + 10 + 3 + 6 = 23. SUM(Цена за единицу товара) равна 220 + 85 + 220 + 40 = 565. Умножьте их, и вы получите 12,995, потому что 23 × 565 = 12 995.

Прежде чем читать дальше, скажите, почему 12 995 намного больше, чем 2 630. Это не округление и не двойное соединение. Скажите, из чего состоят эти лишние 10 365?

Это пары, которых никогда не было. Умножение двух сумм аналогично суммированию каждого количества, пересекающегося с каждой ценой: четыре единицы заказа 1001 по цене 85 заказа 1002, шесть единиц заказа 1004 по розничной цене 220 и так далее для всех шестнадцати комбинаций. Четыре ряда образуют 4 × 4 = 16 пар. Только четыре из них настоящие, те, у которых количество и цена совпадают, и эти четыре — 2630.

Разрыв равен остальным двенадцати: 12 995 минус 2 630 = 10 365.

Запустите то же самое для всех шестнадцати заказов, и цифры станут громче. SUMX возвращает 9,890. SUM(Единицы) — 101, SUM(Цена за единицу товара) — 2125 и 101 × 2125 = 214,625. Шестнадцать рядов означают 16×16 = 256 пар, из них 240 — вымышленные.

Вот рабочая мера.

Total Revenue =
SUMX ( Orders, Orders[Units] * Orders[UnitPrice] )

Прочтите это как два аргумента, выполняющих две работы. Orders это стол, по которому можно ходить. Orders[Units] * Orders[UnitPrice] это расчет, который нужно бежать, стоя на каждом его ряду. SUMX добавляет шестнадцать результатов.

4. Почему никто не улавливает: разрежьте на одну строку, и обе согласятся

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

Да, именно так, и именно эту часть стоит вынести со страницы.

Отфильтруйте визуальное изображение только по заказу 1001. SUMX проходит одну строку и возвращает 4 × 220 = 880. Разбитая мера вычисляет SUM(Единицы) по этой строке, которая равна 4, и SUM(Цена за единицу товара) по этой одной строке, которая равна 220, а затем умножается: 4 × 220 = 880. Идентично.

Это не совпадение в отношении этого приспособления. В одной строке доступна только одна пара, поэтому «каждая пара» и «настоящая пара» представляют собой один и тот же набор. Эти две меры не могут противоречить друг другу.

Световому ряду нужна своя линия, потому что именно она показывает, где заканчивается соглашение. Лампы четырех порядков по 6, 12, 7 и 15 штук, все по 40. СУММ дает 240 + 480 + 280 + 600 = 1600. Разбитая мера дает SUM(Единицы) = 40 и SUM(Цена за единицу товара) = 160 и 40 × 160 = 6400. В четыре раза больше, потому что четыре ряда означают шестнадцать пар, а реальными являются только четыре.

Таким образом, тест, который доказывает эффективность меры, — это тест, который не может потерпеть неудачу. Проверка одной строки пропускает меру, которая в общей сложности ошибочна в шестнадцать раз. Это та же форма, что и заливка Excel, первая строка которой правильная как в рабочей, так и в сломанной версии, и именно поэтому в конце этой страницы принято говорить об итогах, а не об осторожности.

5. Что на самом деле делает SUMX с каждой строкой

Майкрософт называет SUMX функция итератораи определяет категорию в одном предложении: «Функция DAX, которая перечисляет все строки заданной таблицы и оценивает заданное выражение для каждой строки».

Слово, которое имеет значение под этим, контекст строки. Контекст строки — это модель, которая знает, в какой строке она находится, что делает Orders[Units] означают число, а не целый столбец. В глоссарии Майкрософт это ясно сказано: контекст строки «представляет «текущую строку» и используется для оценки формул и выражений вычисляемых столбцов, используемых итераторами таблиц».

Итак, последовательность внутри SUMX(Orders, Orders[Units] * Orders[UnitPrice]) является:

1. Возьмите строки заказов, которые прошли фильтры на визуальном элементе.
2. Встаньте на первый. Orders[Units] это 4 и Orders[UnitPrice] равно 220, поэтому выражение равно 880.
3. Переходите к следующему. Повторить. Шестнадцать раз.
4. Сложите шестнадцать результатов и верните одно число.

Ничего не сохраняется. В конце столбца доходов нет, и в этом разница между ним и столбцом доходов. Расчетный столбец. Вычисляемый столбец вычислит 880 один раз, сохранит его в файле и будет использовать память навсегда. SUMX вычисляет его, когда визуальный элемент запрашивает, и отбрасывает его.

Лучше знать, чем узнавать одну вещь: контекст строки ничего не фильтрует. Расположение в строке 1001 не ограничивает другие таблицы этим порядком. Ссылка на меру внутри итератора меняет это, и это меняет молча, что является скрытым люком. РАСЧЕТ и фильтрация контекста. Прочтите это, прежде чем писать SUMX(Orders, [Some Measure]).

А теперь представьте, что это происходит на столе, который у вас есть. Возьмите свою собственную таблицу фактов, найдите в отчете одно число, которое нужно было вычислить, а не искать, и скажите вслух, по какой таблице будет идти SUMX и какое выражение будет в каждой строке. Две штуки, вот и весь звонок.

6. Остальные члены семьи X

SUMX не является чем-то особенным. У каждого агрегата в DAX есть двойник X, и все они принимают одни и те же два аргумента в одном и том же порядке: таблица, затем выражение для вычисления в каждой ее строке. Изучение SUMX научит вас всему этому одновременно.

CONCATENATEX стоит попробовать в первую очередь, потому что он делает идею построчной записи наглядной. Он принимает необязательный третий аргумент, разделитель, и именно так в заголовке отчета вместо числа отображается «Стол, Стул, Лампа».

У AVERAGEX есть одно поведение, о котором стоит знать, прежде чем оно вас удивит в матрице. Майкрософт заявляет об этом прямо: «Когда нет строк для агрегирования, функция возвращает пустое значение. Когда есть строки, но ни одна из них не соответствует указанным критериям, функция возвращает 0». Пустая ячейка и нулевая ячейка означают разные вещи, и только одна из них рисует полосу.

7. Неправильная цифра, которая выглядит правильно: среднее процентное значение

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

Это не так, и в отличие от примера с доходами, разрыв здесь достаточно мал, чтобы его можно было отправить.

Стоимость зависит от продуктов, а не от заказов, поэтому маржа — это число, которое нигде не существует и которое необходимо создавать. RELATED это то, что позволяет строке Заказы читать строку своего собственного продукта на другой стороне отношения.

Total Margin =
SUMX (
    Orders,
    Orders[Units] * ( Orders[UnitPrice] - RELATED ( Products[Cost] ) )
)

На единицу то есть 220–140 = 80 на столе, 85–52 = 33 на стуле и 40–22 = 18 на лампе. В шестнадцати заказах имеется 23 письменных стола, 38 стульев и 40 светильников, поэтому общая прибыль равна 23 × 80 + 38 × 33 + 40 × 18 = 1840 + 1254 + 720 = 3,814.

Выручка 9890. Таким образом, смешанная маржа равна 3814 / 9890 = 38.56%.

Теперь версия, которая усредняется. Каждый заказ на стол выполняется по 80/220 = 36,36%, каждый стул по 33/85 = 38,82%, каждая лампа по 18/40 = 45,00%, независимо от количества. Существует семь заказов на столы, пять заказов на стулья и четыре заказа на лампы, поэтому:

Average Margin % =
AVERAGEX ( Orders, DIVIDE ( [Order Margin], [Order Revenue] ) )

39,29% против истинных 38,56%. Ошибочно на 0,73 процентных пункта и вполне правдоподобно в карточке на приборной панели.

Причину стоит сказать одной строкой, ведь это целый класс ошибок. Среднее значение дает каждому заказу один голос. Лампы занимают 4 из 16 заказов, поэтому они приносят 25% голосов, но приносят только 1600 из 9890 доходов, что составляет 16,18%. Лампы также имеют самый высокий процент маржи, поэтому их избыточный вес увеличивает средний показатель.

Отношение итогов не является суммой отношений. Исправление состоит в том, чтобы разделить один раз в конце:

Margin % = DIVIDE ( [Total Margin], [Total Revenue] )

Из этого следует правило: СУММКС числитель, СУММКС знаменатель и разделите эти два числа. Никогда не усредняйте процент, который уже был соотношением, если только кто-то не сказал вам в письменном виде, что он хочет, чтобы каждый заказ учитывался одинаково.

8. Когда СУММ — правильный ответ

Ничто из вышеперечисленного не является аргументом в пользу повсеместного использования итераторов. Собственные рекомендации Майкрософт на самой странице SUMX противоположны: «Если вам не нужно фильтровать столбец, используйте функцию SUM».

СУММ верен, когда значение уже находится в столбце, который охватывает большую часть обычной модели. SUM(Orders[Units]) равно 101, и итератор ничего к этому не добавил бы. Таблицу фактов, в каждой строке которой хранится расширенная сумма, следует суммировать, а не выводить заново.

Стоит упомянуть и второй случай, потому что он выглядит как лень, но таковым не является. Если в вашей таблице фактов уже есть столбец сохраненного дохода, его использование будет более точным, чем повторный расчет единиц, умноженных на цену, поскольку сохраненное значение — это то, что фактически было выставлено в счете. Округление, скидки и переопределение цен — все это хранится в этом сохраненном числе и ни одно из них не участвует в умножении.

И если вы обнаружите, что пишете SUMX(Orders, Orders[Units]), с пустым столбцом в качестве выражения и без каких-либо вычислений, это СУММА с дополнительным синтаксисом. Напишите СУММУ.