Условное форматирование в матрице с помощью Calculation group
Всё больше компаний начинает использовать для создания отчетов BI системы: PowerBI, Qlik, Tableau и прочие. Но бывает очень трудно отказаться от использования в визуализации табличек с множеством показателей, которые привыкли использовать в Excel. Только смотреть на такие таблички и находить в них ценную информацию для конечного пользователя затруднительно, поэтому хочется подсветить отклонения, чтобы облегчить данную задачу. Для этого в PowerBI существует функционал условного форматирования. Мы можем форматировать меры как на основе их значений, так и на основе значений других мер или столбцов. И тут мы сталкиваемся с проблемой, которую порождают таблицы с множеством показателей, чтобы отформатировать меру на основе относительного показателя: сравнение со средним за период или сравнение с предыдущим периодом, либо сравнение план/факт и так далее - мы будем вынуждены создать ещё столько же мер. И, чем больше и сложнее будет наша модель, тем труднее будет управлять этим количеством мер. И тут нам на помощь приходят #CalculationGroup(CG), которые позволяют написать всего одно вычисление, точнее два, но об этом чуть позже, а не создавать дополнительную меру на каждую основную. Создать CG можно с помощью надстройки #TabularEditor.
Итак, имеется визуализация в виде матрицы, у которой в строках находятся даты, а далее множество мер (в примере будет три, но этого достаточно).
Мы хотим, чтобы меры [Sum SubTotal] и [Average Freight] подсвечивались красным при отклонении значения на дату от среднего значения за период более чем на 15%, а для меры [Sum TaxAmt] при отклонении более чем на 15% от значения предыдущего месяца. Мы можем конечно сделать своё условие для каждой меры, но тогда не будет смысла использовать CG, так как проще создать дополнительные меры.
Открываем TabularEditor. Нажимаем правой кнопкой мыши по папке Table => Create New => Calculation Group, ну или сразу нажимаем ALT+7. Задаем имя для CG - в примере названо незамысловато CG1. Затем нажимаем правой кнопкой мыши по созданной CG => Create New => Calculation Item. Задаем имя для вычисления, например FormatString. Теперь в окне Expression Editor для созданного Calculation Item вводим вычисление, которое будет применяться к мерам в нашей визуализации:
Так как в примере мера [Sum TaxAmt] должна форматироваться по другому правилу, чем две другие, я использую функцию SWITCH для проверки "какая мера сейчас вычисляется". Функция ISSELECTEDMEASURE возвращает TRUE, если в данный момент вычисляется мера [Sum TaxAmt]. SELECTEDMEASURE - вычисляет текущую меру. С помощью CALCULATE и DATEADD получаем значение текущей меры за предыдущий месяц, после чего проводим сравнение. Вместо AVERAGEX, которая считает простое среднее для всех строк, я использовал MEDIANX, чтобы минимизировать влияние выбросов, которое приходится на первое число месяца. И в конце я использую ABS, чтобы получить абсолютное значение отклонения, так как по условию нам не важно в какую сторону отклонение - главное, что оно есть. Но, если мы применим данную CG в таком состоянии: для этого поместим столбец Name из CG1 в боковую панель фильтров нашего визуала (будьте внимательны - необходимо помещать именно в раздел данного визуала, чтобы CG действовала только на него), то в ячейках матрицы у нас будут десятичные цифры относительного отклонения, а нам требуется, чтобы показывались значения исходных мер. И вот здесь есть ещё одно замечательное свойство CG - их форматирование также можно задать с помощью выражения. Для этого на вкладке Expression Editor необходимо переключить Property с Expression на Format String Expression. Главное здесь, что выражение должно возвращать результат в виде текста, поэтому в ведём следующее выражение: FORMAT(SELECTEDMEASURE(),"#,0"). Нажмем применить и перейдем в PowerBI. Получился не тот результат, который мы ожидали. В данном случае SELECTEDMEASURE возвращает уже модифицированную меру, которая была вычислена с помощью выражения из Expression. Поэтому немного модифицируем наше выражение в Format String Expression, добавив CALCULATE и REMOVEFILTERS, что позволит удалить фильтр(влияние) CG: FORMAT(CALCULATE(SELECTEDMEASURE(),REMOVEFILTERS('CG1'[Name])),"#,0").
Теперь мы снова видим наши исходные значения мер - осталось только настроить условное форматирование для каждой из мер. Выделяем матрицу - Панель визуализации - Раздел Поля - Разворачиваем флажок каждой меры - Условное форматирование - Фон - Выбираем тип Правила - и задаем условие, что если значение больше или равно 0.15 и меньше или равно максимуму, тогда красить красным - ОК.
Теперь сколько бы мы мер не создавали и не добавляли в матрицу, нам останется только настроить для них правила условного форматирования, которое будет выполняться на основе отклонения от среднего, если только не прописать свою логику для новой меры по аналогии с мерой [Sum TaxAmt].
Ссылка на файл с примером