Top 6 Power BI Conditional Formatting Options

May 13, 2020 | Power BI Hints and Tips

If you haven’t some across conditional formatting before then it allows formatting to automatically be applied based on the data.  If the data changes then the formatting automatically updates.  Conditional formatting has been a useful tool in Microsoft Excel for years and has gradually evolved in Power BI to have much of the same functionality.

Conditional formatting is only available in the text based visuals (table and matrix) and is in the visualizations pane on the Format tab.  Each option has extra flexibility by using the Advanced Controls option.

Here are my top 6 possible Power BI conditional formatting options:

1. Background Colour – Colour Scales

This changes the background colour of the cells in the column chosen.  By default the formatting is based on the column initially chosen but this can be altered to any field in any table.  If the field is numerical this defaults to working on a sum of the field but can be altered to average, count and more.  If it is a date or text it defaults to performing a count.

Initially a colour is chosen for the highest and lowest values and the shading will be graduated between these.  If diverging is chosen then a middle colour can be selected too.

If preferred a custom value can be used for the highest, lowest and middle values.  In this case anything below the lowest value gets shaded in the first colour, anything exactly equal to the middle value gets shaded in the middle colour and anything above the highest value gets shaded in the last colour.  The other figures are graduated between these.

There is also an option for choosing the formatting for blank values to match the formatting for a zero value, not to format or to choose a specific colour.

2. Background Colour – Rules

This changes the background colour based on a series of rules.  At the top of the dialogue box colour scales is changed to rules.  Any number of rules can be added by selecting the add rules button.  This allows for ranges of numbers or specific text to be formatted in a determined way.  With numbers the ranges can be specific or based on percentages of the numbers.

For example, numbers 0 to 25 could be red, 26 to 50 could be orange, 51 to 75 yellow and over 75 green. 

3. Font Colour

Power BI Conditional formatting for font colour is identical to that for background colour.

4. Data Bars

Data bars are only available for numerical fields.  A graphical bar shades across the cell based on the size of the number.  By default this runs from the lowest to highest values but these ca be adjusted to specific custom figures if required.

The colour of positive and negative bars can be set appropriately.  The vertical axis colour can also be changed (only appropriate if there are both negative and positive figures).  The bars can be set to shade from left to right or right to left and finally the bar can be shown without the figures behind them if preferred.

 5. Icons

By default there are a range of preset icon sets ranging from 3 to 5 icons in each.  The icon can set to be to the left or the right and at the top, middle or bottom of the cells.  The order of the icons can be reversed depending on whether high or low numbers are viewed to be best.

The icons are determined based on the percentage of the highest figure but this can be altered to be determined by specific numbers if this is more appropriate.

In theory the icons in different icon sets can be mixed and matched but this is rarely as useful as those provide by Power BI.

6. Web URL

If you have a column or measure that contains website URLs, you can use conditional formatting to apply those URLs to fields as active links.

As you can see there is a wide variety of options available to ensure that your tables can be formatted in the most appropriate way to emphasise particular records quickly and effectively.
Want to learn more about Power BI? Then email to discuss how I can help or have a look at the Power BI Courses I run.