Excel DAX - ранжирование и сравнение значений

Если хочешь показать только верх n количество элементов в столбце или сводной таблице, у вас есть два следующих варианта:

  • Вы можете выбрать n количество верхних значений в сводной таблице.

  • Вы можете создать формулу DAX, которая динамически ранжирует значения, а затем использует значения ранжирования в срезе.

Применение фильтра для отображения только нескольких первых элементов

Выбирать n количество верхних значений для отображения в сводной таблице, выполните следующие действия:

  • Щелкните стрелку вниз в заголовке меток строк в сводной таблице.
  • Щелкните Фильтры значений в раскрывающемся списке, а затем щелкните Первые 10.

Появится диалоговое окно Top 10 Filter (<имя столбца>).

  • В разделе «Показать» выберите в полях слева направо следующее.
    • Top
    • 18 (Количество верхних значений, которые вы хотите отобразить. По умолчанию - 10.)
    • Items.
    • В поле по выберите Количество медалей.
  • Щелкните ОК. 18 лучших значений будут отображаться в сводной таблице.

Преимущества и недостатки применения фильтра

Преимущества

  • Это просто и удобно.
  • Подходит для таблиц с большим количеством строк.

Недостатки

  • Фильтр предназначен исключительно для отображения.

  • Если данные, лежащие в основе сводной таблицы, изменяются, необходимо вручную обновить сводную таблицу, чтобы увидеть изменения.

Создание формулы DAX, которая динамически ранжирует значения

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

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

  • Нулевое значение для максимального значения в таблице.

  • Равные значения будут иметь одинаковое значение ранга. Еслиn количество значений равно, следующее значение после равных значений будет иметь непоследовательное значение ранга, добавляющее число n.

Например, если у вас есть таблица «Продажи» с данными о продажах, вы можете создать вычисляемый столбец с рангами значений суммы продаж следующим образом:

= COUNTROWS (FILTER (Sales,  
   EARLIER (Sales [Sales Amount]) < Sales [Sales Amount])
) + 1

Затем вы можете вставить слайсер в новый вычисляемый столбец и выборочно отображать значения по рангам.

Преимущества и недостатки динамических рангов

Преимущества

  • Ранжирование выполняется в таблице, а не в сводной таблице. Следовательно, может использоваться в любом количестве сводных таблиц.

  • Формулы DAX рассчитываются динамически. Следовательно, вы всегда можете быть уверены в правильности ранжирования, даже если базовые данные изменились.

  • Поскольку формула DAX используется в вычисляемом столбце, вы можете использовать ранжирование в срезе.

  • Подходит для таблиц с большим количеством строк.

Недостатки

Поскольку вычисления DAX требуют больших вычислительных ресурсов, этот метод может не подходить для таблиц с большим количеством строк.