DISTINCTCOUNT в Power Pivot: почему общий итог меньше суммы строк

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

Разобрать пересечение на пяти строках

Пусть в таблице Sales есть CustomerID и Category. Клиент A купил кофе дважды, B — кофе и чай, C — только чай. Всего пять строк покупок. DISTINCTCOUNT по CustomerID в кофе вернёт два уникальных значения: A и B. В чае — B и C. На уровне всего отчёта множество клиентов состоит из A, B и C, поэтому результат равен трём. Повторная покупка A не добавляет ещё одного клиента.

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

Customers := DISTINCTCOUNT(Sales[CustomerID])

Когда сумма строк действительно нужна

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

Customer Category Participations :=
SUMX(
    VALUES(Sales[Category]),
    CALCULATE(DISTINCTCOUNT(Sales[CustomerID]))
)

Теперь первая мера возвращает в общем итоге три, вторая — четыре. В отдельных строках категорий обе покажут два. Это не два способа получить одинаковый итог, а два разных бизнес-показателя. Если в сводную добавить месяцы, вторая мера по-прежнему складывает категории в текущем временном контексте; она не становится автоматически суммой всех видимых ячеек по двум измерениям. Для суммы сочетаний категория–месяц нужно отдельное определение единицы участия.

Проверить пустые ключи и справочники

DISTINCTCOUNT учитывает BLANK как отдельное значение. Если у покупки потерян CustomerID, итог может включать одного «пустого клиента». Перед расчётом проверьте количество таких строк и решите, как показывать неизвестных покупателей. Для исключения пустого идентификатора можно использовать CALCULATE с условием NOT ISBLANK(Sales[CustomerID]), но доля исключённых покупок должна оставаться видимой. Удаление строк с плохим ключом способно скрыть проблему исходных данных.

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

Объяснить итог пользователю отчёта

Рядом с количеством клиентов полезно показать число покупок и название периода. Для учебного набора это пять покупок, три клиента, четыре участия в категориях. Эти три числа согласованы и отвечают разным вопросам: объём операций, размер аудитории и ширина её покупательского поведения. Если цель — рассылка по всей базе, адресатов будет три; если планировать отдельную коммуникацию по каждой категории, B может участвовать в двух списках.

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

Продолжить работу с данными

Курс «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов» разбирает, почему мера показывает разные числа при разных фильтрах, а также SUMX и неверные итоги в дашборде. Это поможет задавать смысл показателя до написания формулы и строить сводные, которые пользователь сможет проверить на конкретных покупках. Откройте программу платного курса «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов».