Спикер Алексей Борисов

Магазин электроники продаёт планшеты, ноутбуки, аксессуары. Данные разбиты на три файла → сделки (дата, модель, количество), товары (модель, цена продажи, закупочная цена), магазины (название, адрес, рейтинг). Нужно вывести выручку и прибыль по каждому магазину. Количество лежит в одной таблице, цена — в другой. Классическая ситуация, ради которой Power Pivot и существует.

Почему обычной SUM недостаточно

SUM складывает значения одного готового столбца. Столбца «выручка» в исходных данных нет → его нужно получить как количество × цена по каждой строке, и только потом сложить результаты. Для этого в DAX есть SUMX.

Функция SUMX — это SUM Expression, то есть сумма выражения по каждой строке такой-то таблицы. Она работает не как сумма одного готового столбца, а как сумма выражения, а потом мы это выражение прописываем.

Логика строится «с конца» → сначала определяешься с финальной мат-операцией (сумма, среднее, минимум), затем смотришь, есть ли уже готовый столбец. Если есть — SUM. Если нет, а результат получается из выражения по строкам — SUMX.

Шаг 1 → связываем таблицы правильно

Без установленной связи RELATED не сработает — а без RELATED SUMX не достанет цену из соседней таблицы. В модели данных тянешь модель из сделок к модели в товарах, название магазина из сделок к названию в справочнике магазинов. Связь всегда один-ко-многим → в справочнике поле уникально (одна модель — одна цена), в таблице событий повторяется (одну модель продаём многократно).

Частая ошибка новичков — связывать по типу товара (в обеих таблицах встречается «планшет»). Такая связь многие-ко-многим, Power Pivot её блокирует. Связывать нужно по полю, которое однозначно определяет строку в справочнике.

Если проданный планшет — это может быть айпад, а может быть какой-нибудь амазон, нам ничего не понятно. Но когда мы узнаем, что продали айпад эйр — это понятная история. Мы ищем в товарах айпад эйр, он стоит 45 000.

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

Шаг 2 → мера «Общая выручка»

Power Pivot → Управление → в окне сводной таблицы Меры → Создать меру. Таблица — «Сделки», имя — «Общая_выручка», формула:

= SUMX(
    Сделки,
    Сделки[Количество] * RELATED(Товары[Цена])
)

Разбор по слоям. SUMX принимает два аргумента. Первый — таблица, по каждой строке которой движемся; это всегда таблица событий, потому что именно в ней лежат факты. Второй — выражение, вычисляемое для каждой строки. Количество берём напрямую из «Сделки», потому что столбец находится в той же таблице, по которой идём. А цена — в другой таблице, значит нужна функция-транспорт.

RELATED — это как бы функция транспорта. У нас заранее есть связи, которые мы установили. RELATED постарается нам потянуть столбец из какой-нибудь другой таблицы. Это ВПР в один шаг.

Внизу окна меры выбираешь формат — валюта, 0 знаков после запятой. Нажимаешь ОК, в списке полей появляется «Общая_выручка», которую кидаешь в сводную по магазинам, категориям, датам.

Шаг 3 → мера «Общая прибыль»

Прибыль = количество × (цена продажи − закупочная цена). И цена, и закупочная цена находятся в таблице «Товары», значит RELATED нужен дважды:

= SUMX(
    Сделки,
    Сделки[Количество] * (
        RELATED(Товары[Цена]) - RELATED(Товары[Закупочная_цена])
    )
)

Скобок стало три — скобка SUMX, внутри неё скобка мат-выражения (цена минус закупка), и каждая RELATED в своих. Если при наборе запутался, пиши каждую часть на отдельной строке — Power Pivot переводы строк внутри формулы не ломают.

Типичные ошибки

Незакрытая скобка

По наблюдениям преподавателей, на скобки приходится девять из десяти ошибок в DAX. Чаще всего забывают закрыть RELATED перед минусом.

RELATED закупочная цена, закрыли скобку RELATED, закрыли скобку мат-выражение цена минус цена, закрыли скобку SUMX. Здесь 1 скобка, 2 скобка, 3 скобка. Вот эту скобку чаще всего забывают.

Excel при незакрытой скобке предложит исправление. Power Pivot просто выдаст ошибку и откажется сохранять меру — особенно это заметно на старых версиях Office, где 13-я вообще не прощает незакрытых скобок, а 16-я научилась некоторые закрывать сама. Лайфхак — в черновике выделяй пары скобок разными цветами, чтобы визуально контролировать вложенность.

Ссылка на чужой столбец без RELATED

Если написать «Товары[Цена]» напрямую, мера не сработает. Любая ссылка на столбец чужой таблицы внутри SUMX проходит через RELATED. Без RELATED можно обращаться только к столбцам той же таблицы, по которой идёт перебор.

Связь по неуникальному полю

Если в справочнике товаров «планшет» встречается сорок раз (разные модели планшетов), связь по типу товара не создастся — Power Pivot требует уникальности в «одной» стороне связи один-ко-многим. Иначе получается связь многие-ко-многим, а такие в модели Power Pivot просто не работают. Решение — связывать по модели (айпад эйр, амазон фаер эйч-ди-7), которая в справочнике одна строка, а в сделках повторяется.

Путаница в формате меры

Формат ставится прямо в окне создания меры — валюта, процент, число, дата. Частый казус: у меры «Общее количество» по умолчанию ставят валюту, и штуки начинают отображаться как рубли. Исправляется в том же окне через «Управление мерами».

Когда SUM, когда SUMX

Правило простое. Есть готовый числовой столбец, который нужно сложить, — SUM. Готового столбца нет, а нужное число получается перемножением или сложением данных по строке, — SUMX. Вторая ситуация типична для моделей с несколькими таблицами: количество в одной, цена в другой, скидка в третьей.

Мера — это не просто формула, а способ хранить вычисление отдельно от конкретной сводной. Создал один раз «Общая_выручка» — и выводишь её в разрезе магазинов, категорий, дат, дней недели, комбинируешь с фильтрами. В отличие от вычисляемого столбца, мера пересчитывается по текущему контексту сводной, не раздувает модель и работает быстрее.

Куда дальше

SUMX и RELATED — фундамент для мер в Power Pivot. Следующий шаг — функция CALCULATE, которая добавляет к мере фильтр контекста: «выручка по розничным сделкам» (количество ≤ 15), «доля магазина в общей выручке», «выручка год-к-году». Подробно разбираем всё в курсе Power Query и программе Excel Эксперт — с живыми файлами, разбором ошибок и практикой на типовых задачах.