TOPN в Power Pivot вернул больше десяти товаров: что делать с ничьими

Мера «выручка десяти лучших товаров» неожиданно включает двенадцать позиций. Формула использует TOPN(10), но на десятом месте три товара с одинаковой выручкой. Для DAX это нормальное поведение: все строки, связанные ничьей на границе отбора, могут попасть в результат. Нужно выбрать правило бизнеса: сохранить все равные результаты или получить строго десять позиций с заранее определённым разрешением ничьей.

Показать, где возникло двенадцать

Возьмём двенадцать товаров с уникальными ProductID от 1 до 12. Первые девять имеют выручку 1000, 900, 800 и далее до 200 рублей. Товары 10, 11 и 12 принесли по 100. Для отбора только по выручке десятая строка задаёт порог 100 рублей, и все три равных товара проходят. Суммарная выручка отбора равна 5700. Отобрать ровно десять можно, если при равной выручке предпочитать меньший ProductID.

Уникальный идентификатор подходит как техническое правило стабильности, когда бизнес не отдаёт предпочтения ни одному из равных товаров. Если предпочтение есть, например меньший остаток или большая маржа, используйте согласованный показатель вторым ключом, а уникальный ID — последним. Не полагайтесь на текущий порядок строк справочника: обновление или загрузка данных может его изменить.

Построить таблицу кандидатов внутри меры

В модели Power Pivot таблица Product содержит уникальный ProductID и связана с Sales один-ко-многим. Сначала создайте меру Revenue. Затем в мере Strict Top10 Revenue получим выбранные товары, добавим каждому выручку текущего периода и удалим товары без продаж. TOPN упорядочит кандидатов по SalesValue вниз, затем по ProductID вверх. Последний ключ уникален, поэтому на границе не останется одинаковых полных ключей.

Revenue := SUM(Sales[Amount])

Strict Top10 Revenue :=
VAR Candidates =
    FILTER(
        ADDCOLUMNS(
            ALLSELECTED(Product[ProductID]),
            "SalesValue", [Revenue]
        ),
        NOT ISBLANK([SalesValue])
    )
VAR Chosen =
    TOPN(10, Candidates, [SalesValue], DESC, Product[ProductID], ASC)
RETURN
    SUMX(Chosen, [SalesValue])

Отбор и отображение — разные действия

На контрольных данных мера вернёт 5500: первые девять товаров плюс товар 10. Если убрать второй ключ ProductID, результат станет 5700. Для проверки количества можно временно заменить последнюю строку SUMX на COUNTROWS(Chosen): получатся десять и двенадцать соответственно. Когда кандидатов меньше десяти, вернётся фактическое число доступных товаров. Создавать искусственные позиции до десятки не требуется.

Эта мера возвращает сумму выбранных товаров. Она сама по себе не превращает строки сводной в список десяти победителей. Для такого списка нужен отдельный фильтр строк или мера включения; не стоит ожидать, что скалярная сумма автоматически скроет остальные товары. Кроме того, TOPN не гарантирует порядок возвращаемой таблицы. Если затем отображаете список, сортировку представления настройте явно по тем же ключам.

Сохранить ожидаемое действие срезов

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

В Excel нужен Power Pivot с поддержкой используемого DAX; пример рассчитан на современные версии Excel для Windows. Зафиксируйте правило ничьей в подписи отчёта и сохраните тест с равными значениями на последнем месте. Тогда обновление данных не будет выглядеть случайной сменой участников десятки, а сумма топа останется воспроизводимой.

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

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