Хитрости сортировки в Power BI

Казалось бы, сортировка - это же просто, да? Что там может быть сложного? :)

Однако даже в Excel есть масса хитростей на эту тему, а уж в Power BI с его множеством связанных таблиц в Модели Данных с сортировкой местами всё совсем не очевидно. Возможно, вы уже даже попадали на эти грабли. Давайте-ка расставим все точки над "ё" в этом вопросе.

Исходные данные

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

Здесь:

• Sales - таблица факта, где в каждой строке фиксируются сделки
• Offices и Products - справочники по филиалам и товарам
• Calendar - календарная таблица (таблица дат)

Вариант 1. Сортировка таблиц

Самый простой случай возникает, когда нам нужно отсортировать Таблицу (Table) или Матрицу (Matrix) добавленную на страницу нашего отчета. Для этого достаточно щёлкнуть левой кнопкой мыши по шапке соответствующего столбца (первый щелчок - сортировка по возрастанию, второй - по убыванию). В шапке появится значок стрелки:

Если во время щелчков по заголовкам столбцов удерживать клавишу Shift, то можно сортировать по нескольким столбцам сразу, т.е. реализовать вложенную многоуровневую сортировку:

Бывают ситуации, когда хочется отсортировать таблицу по произвольным данным, т.е. столбцам, которых сейчас в таблице нет. Например, выполнить сортировку таблицы с суммарный продажами по товарам не по объему продаж, а по количеству уникальных покупателей или темпам роста продаж по сравнению с прошлым годом и т.п. В этом случае можно использовать несколько "костыльный", но вполне рабочий трюк - добавить в таблицу столбец с нужными данными, отсортировать по нему таблицу, а затем скрыть этот столбец, уменьшив его ширину до нуля. Только не забудьте на панели форматирования выключить перенос по словам для этой колонки, чтобы текст в шапке вам не мешал.

Вариант 2. Сортировка диаграмм

Предположим, мы добавляем в свой отчёт диаграмму для визуализации объема продаж по товарам и городам - например, гистограмму с сегментами внутри столбцов. По умолчанию Power BI отсортирует её по убыванию высоты столбцов, т.е. суммарного объема продаж по каждому товару. Чтобы изменить это поведение, можно использовать значок с многоточием в правом верхнем углу диаграммы, где выбрать поле, по которому мы хотим сортировать и направление сортировки:

Не совсем очевидный нюанс состоит в том, что если добавить в Подсказки (Tooltips) диаграммы какое-либо поле или вычисляемый столбец, то он тоже появляется в меню под кнопкой с троеточием и можно будет задавать сортировку даже по нему, хотя в диаграмме он фактически не присутствует:

Здесь же стоит упомянуть, что в Power BI есть специальные типы диаграмм, которые выполняют сортировку автоматически. Из встроенных визуализаций так делает, например, Ленточная (Ribbon) диаграмма, которая сама сортирует сегменты по убыванию внутри каждого столбца, наглядно отображая изменение рейтинга каждой категории легенды:

Вариант 3. Сортировка месяцев

Это классическая "детская" проблема, с которой сталкивается большинство начинающих пользователей при использовании собственной календарной таблицы. С сортировкой дат, лет и кварталов обычно всё хорошо (ибо это числа), а вот при размещении на оси Х текстовых названий месяцев и настройке сортировки по этой оси, месяцы располагаются почему-то по алфавиту.

Чтобы исправить этот момент, нужно переключиться в режим представления данных и, выбрав столбец Месяц (с текстовым обозначением), включить сортировку этого столбца по колонке Номер месяца (где у нас числа от 1 до 12):

Если такого столбца в вашей календарной таблице ещё нет, то его можно легко добавить кнопкой Создать столбец и простой формулой на DAX:

Номер месяца = MONTH(Calendar[Date])

... где Calendar[Date] - имя столбца с подряд идущими датами в вашей календарной таблице Calendar.

После этого в любой визуализации в вашем дашборде месяцы начнут сортироваться в правильной календарной последовательности.

Вариант 4. Нестандартная сортировка

Наконец давайте разберём сценарий, когда нам нужно сортировать (неважно что - таблицу или диаграмму) не по алфавиту, не по возрастанию или убыванию, а в соответствии со какой-то нашей собственной логикой. Например, при сортировке филиалов они должны идти в определенной принятой у нас в компании последовательности - например, сначала Москва и Санкт-Петербург, потом Казань и т.д.

Чтобы такое реализовать, нам потребуется дополнительный столбец (как это было с месяцами), по значениям которого и будет задаваться сортировка. Проще всего добавить его в источник данных, прописав напротив каждого города его порядковый номер при сортировке:

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

После этого сортировка городов будет происходить уже не по алфавиту, а по нашему пользовательскому принципу - в любых таблицах и диаграммах:

Если добавить столбец в источник данных не представляется возможным, а сортируемых объектов (в нашем примере - городов) не очень много, то можно добавить столбец с порядковыми номерами для сортировки уже к загруженным данным в Power BI. Это можно сделать, например, в Power Query с помощью условного столбца: открываем редактор запросов Power Query кнопкой Преобразование данных - далее вкладка Добавление столбца - Условный столбец (Transform Data - Add column - Conditional column):

Если же сортируемых объектов (строк-городов) у вас будет много, то имеет смысл сделать отдельный справочник и загрузить его в Модель Данных и привязать к другим таблицам стандартным образом.

Ссылки по теме

• Основные принципы и этапы создания дашбордов в Microsoft Power BI
• Что такое Power Query, Power BI, Power Pivot, Power Map и зачем они простому пользователю
• План-факт анализ на Модели данных в Power Pivot в Excel