Conditional formatting in tables

With conditional formatting for tables, you can specify customized cell colors based on cell values, or based on other values or fields, including using gradient colors. You can also display cell values with data bars.

To access conditional formatting, in the Fields well of the Visualizations pane in Power BI Desktop, select the down-arrow beside the value in the Values well that you want to format (or right-click the field). You can only manage conditional formatting for fields in the Values area of the Fields well.

Conditional formatting menu

The following sections describe each of these conditional formatting options. One or more options can be combined in a single table column.

Note

When applied to a table, conditional formatting overrides any custom table styles applied to the conditionally formatted cells.

To remove conditional formatting from a visualization, just right-click the field again, select Remove conditional formatting and then the type of formatting to remove.

Remove conditional formatting menu

Background color scales

Selecting Conditional formatting and then Background color scales brings up the following dialog.

Background color scales dialog

You can select a field from your data model to base the colors on, by setting Color based on to that field. In addition, you can specify the aggregation type for the selected field with the Summarization value. The field to be colored is specified in the Apply color to field, so you can keep track. You can apply conditional formatting to text and date fields, as long as you choose a numeric value as the basis of the formatting.

Color based on field

To use discrete color values for given value ranges, select Color by rules. To use a color spectrum, leave Color by rules unchecked.

Background color scales dialog

Color by rules

When you select Color by rules, you can enter one or more value ranges, each with a set color. Each value range starts with an If value condition, an and value condition, and a color.

Color by rules value range

Table cells with values in each range are filled with the given color. There are three rules in the following figure.

Color by rules example

The example table now looks like this:

Example table with color by rules

Color minimum to maximum

You can configure the Minimum and Maximum values and their colors. If you select the Diverging box, you can configure an optional Center value as well.

Diverging button

The example table now looks like this:

Example table with diverging colors

Font color scales

Selecting Conditional formatting and then Font color scales brings up the following dialog. This dialog is similar to the Background color scales dialog, but changes the font color rather than the cell background color.

Font color scales dialog

The example table now looks like this:

Example table with font color scales

Data bars

Selecting Conditional formatting and then Data bars brings up the following dialog.

Data bars dialog

By default, the Show bar only option is unchecked, and so the table cell shows both the bar and the actual value.

Example table with data bars and values

If the Show bar only option is checked, the table cell only shows the bar.

Example table with data bars only

Color formatting by field value

You can use a measure or a column that specifics a color, either using a text value or a hex code, to apply that color to the background of font color of a table or matrix visual. You can also create custom logic for a given field, and have that logic apply the desired color to the font or background.

For example, in the following table there is a color associated with each product model.

ProductName field with color name

To format that cell based on its field value, select the Conditional formatting dialog by right-clicking the Color column for that visual, and in this case, select Background color from the menu.

Select background color from menu

In the dialog that appears, select Field value in the Format by drop down area, as shown in the following image.

Format by Field value

You can repeat that process for the font color, and the result in the visual will be a solid color in the color column, as shown in the following screen.

Format by Field value

You could also create a DAX calculation based on business logic, that outputs different hex codes based on conditions you prefer. This is generally easier than creating multiple rules in the conditional formatting dialog. Consider the ColorKPI field in the following sample image.

DAX calculations

You could then set the field value for Background color in the following way.

Set field color based on a KPI

And you could then get results like the following matrix.

Matrix visual with a KPI value based color

There are many more variations you could create, just by using your imagination and a bit of DAX.

Next steps

For more information, see the following article: