Using the Colour Brewer With Conditional Formatting

The Power BI Desktop May Update introduced conditional formatting of numerical columns in tables. The snapshot below shows a table with two columns, one of which has had conditional formatting applied.

Table With Conditional Formatting

The conditional formatting dialog is a context menu on numeric columns in the values well

Conditiional Formatting Dialog

You can set the colours at the end points of the colour scale, the minimum or maximum values from a colour picker but also choose a customer RGB colour value (for example, #FF0000 representing pure red). There is a lot of choice but very little guidance on the best combination of colours. Fortunately the colour brewer website  can help here by suggesting a few good colour schemes.  This site was developed by Cynthia A. Brewer in the Geography department of Pennsylvania State University.

Colour Brewer Landing Page

The simplest approach is to take the RGB values of the first and last colours in a scheme and enter these as the custom colours for the minimum and maximum values.
For example, the PL column in the table above used # edf8fb as the colour for the minimum value and # 006d2c as the colour for the maximum value

Choosing a custom RGB colour

The conditional formatting dialog allows to choose a diverging colour scheme. This is useful for a range of values centred around zero where you want to emphasize large absolute values either positive or negative. For example, on a column of profit and loss values it is useful to highlight those periods with either large profits or losses.

Table With Diverging Formatting

This is the first appearance of conditional formatting and there are of course some improvements that suggest themselves. It would be helpful to be able to

  • To be able to set some defaults for personal, team or organisational colour schemes – currently every column has to be set up manually
  • To have a set of discrete colours mapped to ranges (as is provided by the colour brewer site)

In the longer run it would be very useful to have conditional formatting based on a categorical measure as well as a numeric range. For example, imagine a DAX calculation that returns either “Breach” or “OK” – we would like to highlight the breaches.  Of course we could fake this now by our DAX calculation return -1 or 1 for OK and Breach respectively.