Conditional formatting of Matrix visual in Power BI
Conditional formatting for matrix in Power BI is a nifty feature which helps in giving a quick visual overview of the statistics. Be it the reliability measure, sales quotas met etc, conditional formatting helps to communicate the statistics effectively. A simple example is shown below.
Different types of conditional formatting
Conditional formatting settings are available in format tab of the visual. Each fields can be separately formatted using any of the combination of below options.
- Background color
As the name suggests, as per the configuration, a background color for the matrix cells can be set. Once you turn on the background color, Power BI automatically assigns colors based on default out of the box preset. But various customization options are available to configure this.
For customization, you can use the ‘Advanced controls’ option. Once you open advanced controls dialog, you see various options as detailed below.
Format by: Lets you choose the category of configuration for customization. As of now, we have 3 options:
a. Format by ‘Color scale’
As the name suggests, for numerical data, you can set the color scale of a cell’s background with colors for minimum and maximum possible values. There is an option to set diverging color scale as well which allows you set a mid range color. There is also a configuration option available to handle the missing values.
You can also choose the summarization option as per the requirement like sum, average etc.
b. Format by ‘Rules’
Using this option you can set background color of cells with pre-defined rules. This option is handy for scenarios like reporting QoS or reliability. An example would be:
>99.99% — Green
>99% & <99.99% — Orange
<99% — Red
Power BI also has easy validation mechanisms on rules set and shows notification for each rule, if there is any error. Until, there are no errors in the rules, the ok button of the dialog is disabled.
c. Format by ‘Field value’
This is a new feature released as part of August update to Power BI. Format by field value will let you use a measure or column that specifies a color, either through a hex code or name and applies that color to the background or font color. You can find more information about this in the official release notes here.
2. Font color
Once you turn on font color, you get same options as available for background in the Advanced controls dialog. Using this you can configure the font color instead of background color. Personally, I feel that formatting by font color adds to confusion more that providing clarity as many of the font colors are not readable apart from a primary set of standard colors. Let me know in comments if you know any use case for this.
3. Data bars
Data bars are one of the key types of conditional formatting in Power BI. They’re great way to show the relative magnitude of values in a set of data. Data bars allows us to visualize and effectively communicate the relative magnitude within matrix column saving a lot of real estate which would otherwise require a separate column graph. An example is shown below:
Once you turn on data bars conditional formatting, again you get to customize based on your needs using Advanced controls dialog. Following options are available for customization.
Here we have various options to select the min/max values, color of the bars and even the direction of the bar. If required, there is an option to just display the bars and not the data itself.
