Вычисляемый столбец и мера в Power BI: решающее правило

1. Развилка и единственный вопрос, который ее решает

Вот вилка выложена полностью, ибо выбор не очевиден с первого раза. Вопрос в следующем: где должен жить этот расчет? Есть два возможных ответа. А Расчетный столбец — это новый столбец, добавленный в таблицу. Power BI вычисляет свое значение один раз для каждой строки в момент загрузки данных, а затем сохраняет каждое из этих значений в модели. Значения больше никогда не изменяются до следующего обновления. А Мера — это хранимая формула, вообще не имеющая сохраненных значений. Power BI вычисляет его заново во время запроса, то есть в тот момент, когда он нужен визуальному элементу, и вычисляет его внутри любых фильтров, которые визуальный элемент имеет в данный момент: выбор срезов, фильтры страниц, строка диаграммы, которую он заполняет.

Что решает между ними – это один вопрос. Зависит ли ответ от того, что отфильтровал зритель? Если ответ меняется, когда кто-то щелкает срез, это, должно быть, мера, поскольку значения столбца были заморожены еще до того, как появилось средство просмотра. Если ответом является фиксированный факт об одной строке, факт, который остается истинным независимо от того, кто что фильтрует, столбец разрешен, а иногда и верен.

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

2. Контекст строки и контекст фильтра в повседневных словах

Перед пояснением: щелчок слайсером меняет карту с 21,4% до 16,7%. Один из двух инструментов мог отреагировать на этот щелчок, а другой — нет. Какой какой?

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

Вот таблица, которую использует остальная часть страницы. Четыре ряда, два товара, два региона.

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

3. Классический провал: столбец полей, суммированный

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

Margin = Sales[Profit] / Sales[Sales]   -- calculated column, one value per row

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

Теперь перетащите этот столбец «Поля» на карточку. Power BI необходимо свернуть четыре сохраненных значения в одно, а для числового столбца по умолчанию используется их суммирование: 50% + 45% + 10% + 15% = 120%. В карточке указано, что общая маржа составляет 120%, что не просто неверно, но и невозможно. Вместо этого переключите агрегацию на среднее значение, и вы получите (50% + 45% + 10% + 15%) ÷ 4 = 30%, что выглядит респектабельно, но все же неверно, потому что позволяет крошечным 100-м продажам продукта A считаться точно так же, как 500-м продукту B.

Истинная общая маржа — это общая прибыль по отношению к общему объему продаж: прибыль равна 50 + 90 + 50 + 45 = 235, продажи — 100 + 200 + 500 + 300 = 1100 и 235 ÷ 1100 = 21,4%. Ни 120%, ни 30% даже близко к этому не относятся.

Скажите своими словами, почему появились 120%, прежде чем читать дальше. Причина в том, что проценты оснований разного размера сложить невозможно. Добавление 50 из 100 к 10 из 500, как если бы оба они были из одной базы, заставляет небольшой продукт говорить в пять раз громче, чем оправдывают его продажи. Столбец заморозил каждое соотношение на уровне строки, поэтому каждое его последующее агрегирование представляет собой агрегирование коэффициентов, а коэффициенты не агрегируются.

4. Правильная мера, проверенная вручную

Решение состоит в том, чтобы заставить подразделение ждать, пока фильтры не выполнят свою работу. Это мера.

Margin % = DIVIDE ( SUM ( Sales[Profit] ), SUM ( Sales[Sales] ) )

DIVIDE — это деление, которое возвращает пробел вместо ошибки, когда нижняя часть равна нулю, что имеет значение в тот день, когда комбинация фильтров не оставляет строк продаж. Форма формулы – весь урок: сначала суммируем, потом делим.

Теперь каждый фильтр, который применяет зритель, выдает честное число, поскольку суммы происходят внутри контекста фильтра. Проверьте каждый вручную:

Одна формула, четыре фильтра, четыре правильных ответа, и мере никогда не сообщали, какой фильтр будет использоваться. Это то, что вам дает «вычисление во время запроса внутри контекста фильтра». Обратите также внимание, что неправильные ответы в колонке даже не всегда являются ошибочными: Восток и Запад говорят, что 60%, в то время как правда — 16,7% против 27,0%, поэтому версия колонки полностью стирает реальную региональную разницу.

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

5. Сколько стоят колонки в хранилище

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

6. Когда в столбце правильный ответ

Перед объяснением: вам нужен слайсер с опциями «Бюджет», «Средний диапазон» и «Премиум». Можно ли измерить мощность этого слайсера?

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

Price Band =
SWITCH ( TRUE (),
    Sales[Sales] < 150, "Budget",
    Sales[Sales] < 350, "Mid-range",
    "Premium"
)

Один абзац на SWITCH(TRUE()), потому что в первый раз это выглядит странно. SWITCH обычно сопоставляет одно значение со списком точных случаев. Вручение этого TRUE() превращает его в лестницу условий, проверяемых сверху вниз, и побеждает первое условие, которое оказывается верным. Это версия аккуратной цепочки IF-ELSE в DAX, и это стандартный способ записи группирующих столбцов без вложения пяти IF друг в друга. В нашей таблице он хранит «Бюджет» для строки 100, «Средний диапазон» для строк 200 и 300 и «Премиум» для строки 500, и эти метки отвечают на решающий вопрос: никакой щелчок среза не может изменить, к какой полосе принадлежит строка стоимостью 500 долларов.

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

7. Неявные и явные меры

Перетащите пустой числовой столбец в визуальный элемент, и Power BI незаметно превратит его в агрегацию, обычно в СУММУ. Эта автоматически созданная оболочка называется неявная мера. Ан явная мера это тот, который вы написали сами с именем и формулой, например, % маржи выше.

Аналитики пишут четкие показатели по трем причинам, которые все были указаны ранее на этой странице. Во-первых, неявное значение по умолчанию — это именно та машина, которая превратила столбец поля в 120%: она суммирует все, что ей передают, независимо от того, означает ли суммирование что-либо. Во-вторых, явная мера несет в себе свое определение, поэтому процент прибыли означает прибыль от продаж в каждом визуальном элементе, каждой странице и отчете каждого коллеги, а не любую агрегацию, которую кто-то оставил выбранной. Общее письменное определение — это большая часть того, что делает метрику заслуживающей доверия. Определение метрик идет глубже в этом вопросе. В-третьих, только явные меры могут содержать реальную логику: РАЗДЕЛИТЬ, фильтры, сравнения времени. Привычка простая и дешевая: даже для простой суммы напишите Total Sales = SUM ( Sales[Sales] ) один раз и повторно используйте его повсюду.

Крайние случаи, которые выдают уверенно неверные цифры

До пояснения: коллега усредняет столбец маржи вместо суммирования и говорит, что проблема устранена. Это?

Усреднение столбца соотношения является той же ошибкой и для более мягкой шерсти. Это ответ на вопрос. (50% + 45% + 10% + 15%) ÷ 4 = 30%, против истины 21,4%. Невзвешенное среднее значение позволяет строке со 100 продажами учитываться так же, как и строке с 500. Оно ближе 120 %, и именно поэтому оно выдерживает проверку.

Коэффициент промежуточного итога не является суммой коэффициентов ниже него. Даже при правильном измерении люди ожидают, что общая строка матрицы будет суммой строк выше. Для соотношения это никогда не так: 16,7% Востока и 27,0% Запада находятся выше 21,4%, а не 43,7%. Это правильное поведение, и оно стоит одного предложения в примечаниях к отчету, поэтому никто его не «исправляет».

Коэффициент, построенный на несвязанных итогах, игнорирует визуальное представление. Оберните суммы в функции, которые удаляют фильтры или строят их не на той таблице, и показатель показывает 21,4% в каждой строке региональной разбивки. Симптом — это мера, повторяющая всюду одно значение; причина в том, что контекст фильтра выбрасывается.

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

Пустые дивизии. Отфильтруйте комбинацию со строками, но с нулевыми продажами и необработанным / ошибки или показывает бесконечность. DIVIDE возвращает пустое значение, а пустая карта — это честное отображение того, что «нет базы для деления».