TOP N и TOP N% с «Прочими» на DAX

Статический топ-5 без «Прочего»

«Покажите топ-5 товаров, остальные не показывайте»

Самый простой сценарий. Используем RANKX для каждой строки визуала и возвращаем значение только для первых 5.

Top 5 Revenue =
VAR _rank =
    RANKX(
        ALL(dim_product[product_name]),
        [Revenue],
        , DESC, DENSE
    )
RETURN
    IF(_rank <= 5, [Revenue], BLANK())

В bar-chart с осью dim_product[product_name] и значением [Top 5 Revenue] показываются только топ-5. BLANK в DAX не отрисовывается, что даёт нужный эффект.

Проблема: нет «Прочего». Остальные товары просто исчезли, но визуальный total ≠ реальный total. Пользователь видит «5 товаров сделали 14.7 млн», а в KPI-карточке выручка 17.7 млн — возникает вопрос «а куда делись 3 млн?». Решается следующим паттерном.

Топ-5 + одна строка «Прочее»

«Топ-5 отдельно, остальные — одной строкой «Прочее»

Нужна отдельная таблица-ось, которая содержит и реальные product_name, и искусственное значение «Прочее». В модели создаётся calculated table:

dim_product_top_axis =
UNION(
    SELECTCOLUMNS(
        dim_product,
        "label", dim_product[product_name],
        "sort_key", dim_product[product_name],
        "is_other", FALSE
    ),
    ROW(
        "label", "Прочее",
        "sort_key", "zzz_Прочее",
        "is_other", TRUE
    )
)

Связь с основной dim_product не делаем — эта таблица работает как disconnected axis, наполняется через меры. Теперь мера:

Top 5 with Others =
VAR _top_n = 5
VAR _current_label = SELECTEDVALUE(dim_product_top_axis[label])
VAR _is_other = SELECTEDVALUE(dim_product_top_axis[is_other])
RETURN
    SWITCH(
        TRUE(),
        _is_other = TRUE,
            // Для строки "Прочее": всё минус топ-N
            VAR _total_all = CALCULATE([Revenue], ALL(dim_product))
            VAR _top_n_sum =
                SUMX(
                    TOPN(
                        _top_n,
                        ALL(dim_product[product_name]),
                        [Revenue], DESC
                    ),
                    [Revenue]
                )
            RETURN _total_all - _top_n_sum,

        // Для реального товара: показать revenue, если он в топ-N
        VAR _this_rev =
            CALCULATE(
                [Revenue],
                dim_product[product_name] = _current_label
            )
        VAR _rank =
            RANKX(
                ALL(dim_product[product_name]),
                [Revenue], , DESC, DENSE
            )
        RETURN IF(_rank <= _top_n && _this_rev > 0, _this_rev, BLANK())
    )

В bar-chart:

• Ось — dim_product_top_axis[label], сортировка по sort_key
• Значение — [Top 5 with Others]
• Условное форматирование: для строки «Прочее» цвет серый (через is_other)

Получаем 5 столбиков реальных товаров + одна серая «Прочее» снизу. Total совпадает с общей выручкой — математика сходится.

Товары, дающие 80% выручки (Парето)

«Покажите все товары, накопительно дающие первые 80% выручки. Остальное — в «Прочее»

Задача ABC-анализа: не «сколько штук», а «сколько даёт ≤ X% выручки». Количество строк зависит от распределения: в ритейле обычно топ-20 товаров дают 80%, в длинном хвосте — 200.

Top 80pct Revenue =
VAR _threshold = 0.8
VAR _current_product = SELECTEDVALUE(dim_product[product_name])
VAR _total = CALCULATE([Revenue], ALL(dim_product))
VAR _cum_revenue_rank =
    CALCULATE(
        [Revenue],
        FILTER(
            ALL(dim_product),
            [Revenue] >= CALCULATE([Revenue], dim_product[product_name] = _current_product)
        )
    )
VAR _cum_share = DIVIDE(_cum_revenue_rank, _total)
RETURN
    IF(_cum_share <= _threshold, [Revenue], BLANK())

Логика:

1. Для каждого товара считаем «накопительную выручку товаров с выручкой не меньше, чем у меня» (это даёт нам место в сортированном топе)
2. Делим на общую выручку — получаем «накопительную долю»
3. Если ≤ 80%, товар попадает в top

По тому же принципу делается вариант с «Прочим» — применяя схему из паттерна 02, но заменяя RANKX-условие на cumulative share.

Параметризация порога. Вместо жёстких 80% сделайте What-If параметр «Порог ABC» с опциями 70/80/90/95. Пользователь двигает срез — видит разные Парето-кривые.

Пользователь выбирает N через срез

«Дайте срез 5/10/15/20, пусть пользователь сам выбирает, сколько показывать»

What-If параметр Power BI. Modeling → New Parameter → Whole Number → значения от 5 до 50 с шагом 5. Power BI создаёт вспомогательную таблицу 'Top N' с колонкой Top N Value и срез.

Top N with Others (Dynamic) =
VAR _top_n = SELECTEDVALUE('Top N'[Top N Value], 5)
VAR _current_label = SELECTEDVALUE(dim_product_top_axis[label])
VAR _is_other = SELECTEDVALUE(dim_product_top_axis[is_other])
RETURN
    SWITCH(
        TRUE(),
        _is_other = TRUE,
            VAR _total = CALCULATE([Revenue], ALL(dim_product))
            VAR _top_n_sum =
                SUMX(
                    TOPN(_top_n, ALL(dim_product[product_name]), [Revenue], DESC),
                    [Revenue]
                )
            RETURN _total - _top_n_sum,

        VAR _this_rev =
            CALCULATE([Revenue], dim_product[product_name] = _current_label)
        VAR _rank =
            RANKX(ALL(dim_product[product_name]), [Revenue], , DESC, DENSE)
        RETURN IF(_rank <= _top_n && _this_rev > 0, _this_rev, BLANK())
    )

Ровно то же, что паттерн 02, но _top_n берётся из срез. Если срез не выбран, дефолт — 5. Удобно для self-service: руководителю проще крутить ползунок, чем просить разработчика поменять число.

Расширение — dynamic title. Текст заголовка визуала тоже может быть динамическим: «Топ-N товаров (ещё M товаров в Прочем)». Это делается через conditional formatting заголовка визуала по мере:

Chart Title =
VAR _n = SELECTEDVALUE('Top N'[Top N Value], 5)
VAR _total_products =
    CALCULATE(DISTINCTCOUNT(dim_product[product_name]), ALL(dim_product))
VAR _others = _total_products - _n
RETURN
    "Топ-" & _n & " товаров (ещё " & _others & " в Прочем)"

Топ-3 товара в каждой категории

«В каждой категории покажите свои топ-3, остальное внутри категории — в «Прочее в Электронике», «Прочее в Одежде» и т.д.»

Кейс посложнее. Нужен ранг товаров внутри их категории, а не глобально. Ключевое слово — ALLEXCEPT: снимаем фильтр по товарам, но сохраняем фильтр по категории.

Top 3 per Category =
VAR _top_n = 3
VAR _this_product = SELECTEDVALUE(dim_product[product_name])
VAR _this_revenue =
    CALCULATE([Revenue], dim_product[product_name] = _this_product)
VAR _rank =
    RANKX(
        ALLEXCEPT(dim_product, dim_product[category]),
        [Revenue], , DESC, DENSE
    )
RETURN
    IF(_rank <= _top_n, _this_revenue, BLANK())

Для версии «Топ-3 + Прочее по категории» нужна более хитрая disconnected таблица: dim_product_top_axis_hier с колонками category + label (label может быть реальным product_name или «Прочее в Электронике»). Логика по аналогии с паттерном 02, но с привязкой к категории.

Сокращённая версия (без «Прочего», только топ внутри категории):

// В матрице: строки category + product_name
// В качестве значения используем Top 3 per Category
// Фильтр визуала: Top 3 per Category is not blank
// Результат: матрица показывает в каждой категории только топ-3

Такая схема покрывает 80% реальных задач «покажи top-N внутри группы». Для «Прочего в каждой группе» дополнительно строится disconnected-таблица по схеме паттерна 02.