Table and matrix conditional formatting

Default and custom conditional formatting rules can be applied to table and matrix visuals to make it easier to identify exceptions and outlier values. Power BI currently supports background color scales, font color scales, and data bar conditional formatting for table and matrix visuals. To apply conditional formatting to a table or matrix, click the drop-down arrow next to the field name of the measure (for example, Internet Net Sales) in the Values field well of the Visualizations pane. A conditional formatting menu item will appear with an arrow providing access to the three types of conditional formatting.

In the following table, data bar conditional formatting has been applied to four measures related to internet sales:

Data bars conditional formatting

Although the visual has been filtered to the top 10 customer state provinces for France, it would be difficult or time-consuming to gain insight from the 40 distinct data values. The length of the data bars helps to call out high or low values and alternative colors can be applied per measure.

The direction of data bars is particularly helpful in distinguishing negative from positive values per the Internet Net Sales (YOY YTD %) measure in the preceding example visual. For large table and matrix visuals with many values, or when the relative differences between values are more important than the individual values themselves, the option to show only the data bar can be very useful.

Custom conditional formatting rules can be applied to the background and font color scales of table and matrix visual cells similar to Microsoft Excel. In the following example, the Color by rules option is enabled and Rules are defined to format the cells of a measure as green if over 25%, yellow when between -25% and 25%, and red if the value is less than -25%:

Custom conditional formatting rules

The conditional formatting rules are evaluated from the bottom to the top. Therefore, if a cell meets the condition of multiple rules, the lower rule will be applied. The order of rules can be adjusted via the up and down arrows to the right of the color icons.

Multiple conditional formatting types can be applied against the same measure. For example, the same three conditional rules used for the background color scales in the preceding image could also be implemented as font color scale rules. However, the font colors specified for each rule (for example, white) could be chosen to contrast with the conditional background colors (for example, red) to further help call attention to the value.

As of the November 2017 release of Power BI Desktop, only hardcoded values can be specified in the custom formatting rules. In a future release, DAX measures will likely be supported as inputs to conditional formatting rules. This functionality would make it easier to implement more complex rules, such as greater than the prior year-to-date sales value.

Table of Contents for Table and matrix conditional formatting

Create new playlist

Sign In

Sign Up

Table of Contents for
Table and matrix conditional formatting