The complete guide to Conditional Formatting in Power BI

Power BI Example File - The complete guide to Conditional Formatting in Power BI

1 file(s) 1,021.88 KB

Dataset Conditional Formatting

1 file(s) 6.99 MB

In this article you will learn how to apply conditional formatting in Power BI. The first part of this blog explains the basics of Conditional Formatting: where to find it and how to apply the different formatting options. The second part contains three step-by-step cases. The three cases are:

Case study 1 : Conditional Formatting a table and Card colours based on hard defined Rules

In this scenario we are going to apply conditional formatting on a table that contains Products and Sales amount. In this case we want to apply a red Colour if Sales per Product is lower than €20.000, and applying a green Colour if the amount is greater than €50.000 making a card visual that turns green or red based on the same rule. You can find the end result as a PBIX-file at the top of this blog.

Case study 2 : Conditional Formatting based on Measure SalesAmount Vs Target

In this scenario we are going to give a column in a table a green or red background depending on whether a product turnover has been achieved. Each product has a target value in the dataset. We use a Measure to compare whether the SalesAmount is larger than the Sales Target Product turnover. You can find the end result as a PBIX-file at the top of this blog.

Case study 3: Conditional Formatting of other elements: Changing Background Colour based on Rules

In this example we are going to give the background Colour of a Card Visual a conditional formatting. You can find the end result as a PBIX-file at the top of this blog.

Three ways to access Conditional Formatting in Power BI

There are three ways to find Conditional Formatting. Below you will find a description of all three methods.

Method 1: Go to the Visualization Pane –> Tab Paint roller –> “Conditional Formatting”

The first thing you can choose is the column you want to format. Below you can see that there are three or four options (depending on the data type of the selected column) to apply colours. Use the toggles to turn on a conditional formatting option.

By default it will apply Colour Scale formatting. Clicking Advanced controls will lead you to the Formatting Dialog where you can choose your own formatting rules. In the next section you will find more information on all the settings.

If you choose to Format a Column which has numbers as Data type (e.g. SalesAmount) you can choose “Data bars”. This gives you the option to show bars as background in the column. Also you can apply conditional formatting on the bars itself by clicking Advanced Controls:

Method 2: Visualization Pane –> Field Tab –> Click on a field to access the menu –> “Conditional formatting”.

Method 3: Clicking on three vertical dots

This one is really hidden: For some elements of a visual it is possible to apply Conditional formatting. You can recognize it by hovering over a setting and when Conditional formatting is applicable you will see three vertical dots in the right corner. By clicking on the three dots you will get an option “Conditional formatting” which will lead to the Formatting Dialog.

Using the Conditional Formatting Dialog

By clicking on the “Advanced Controls” hyperlink the Formatting Dialog will open. By default it shows the Colour Scale settings:

Format by:

First thing to note there is a Format by dropdown with the following options:

Color Scale: Apply a Colour to lowest and highest value in the selected field. All values between will gradually change Colour. You can change “Lowest value” to Numbers to manually select the min and max values. If you check the option “Diverging” it’s also possible to define a Colour for the Center (mid) Value.

Rules: Here you can choose to set a Colour based on manually defined values in combination with if/and statements:

How rules are working

Please note that by default the value fields are always filled with zero (0). You can delete those values and you will see Minimum and Maximum values:

You can interpret those values as follow. The Minimum is the undefined minimum that is shown in the column. This can be -5, -10.000, 5000, as long as it’s lower than the value in de AND statement of the rule. The same goes for the Maximum where it will look at de undefined maximum for the selected the column. Now to fully understand this concept take look at the example below and answer yourself this question: Which numbers are coloured red according to the set rules?

The answer is: All negative numbers/numbers smaller than 0. In the beginning it can seem strange that you cannot indicate “smaller than” at the first number. So you always have to fill in two values. By user of Minimum you can still specify “Less than”.

Also note that you can choose between “Number” and “Percent” as a value.

Fields: This option is really cool! In many cases you don’t want to hard code the rules, but ideally you want to apply colours based a Measure of Calculated column. With Format By Fields you use the outcome of a formula in a Measure or Column, which has to be a Colour label or a hex code, to apply the Colour dynamically. For example: You can make a Measure which checks if the SalesAmount is greater than target and give it label “green”, else it will get label “red”. Of course you can make this much more complex as you wish as long as the outcome is a Colour definition. As documented in the official Microsoft documentation you can use any of the values listed in the CSS Colour spec at https://www.w3.org/TR/css-Colour-3/ to Colour your visuals:

Hex rgb: 3, 6 or 8 digit hex codes e.g. #3E4AFF. Make sure you include the # symbol at the start of the code. “3E4AFF” is not accepted.

RGD Decimals: RGB or RGBA values e.g. RGBA(234, 234, 234, 0.5)

HSL or HSLA values e.g. HSLA(123, 75%, 75%, 0.5)

Colour names e.g. Green, SkyBlue, PeachPuff

Use this Colour picker to quickly choose your favorite Colour:

https://www.w3schools.com/colours/colours_picker.asp

Based on field

Next thing you can configure is which field is used to select the value on which formatting needs to apply. By default it selects the column which you choose to apply the conditional formatting to (in this case EnglishProductName). So that’s the reason why it uses Count. In this case it doesn’t make sense because we want to apply the formatting based on total Sales Amount. To do so you can click on the drop down menu and click on one of the tables or use the search functionality to find SalesAmount.

Summarization

Here you can choose the summarization of the column. Note: If you have chosen a Measure under “Based on field” you can’t choose anything here because the summarization is already determined in the Measure.

Default formatting (Colour Scale only)

You can choose how a blank value will be handled. The options are:

Don’t format: Apply no Colour to blank values

As zero: Handle blank value as ‘0’.

Specific Colour: Apply specific Colour to all blank values

Case Study 1: Applying conditional formatting on a table column based on rules

In this scenario we are going to apply a background Colour on all Sales below 20.000

Step 1: Open the Conditional formatting Dialog

In this example we are going to give cells in the column EnglishProductName the Colour that Measure indicates. Select the table visual, go to the Paint Roller under the Visualizations Pane and click on the tab “Conditional Formatting”. Select the column you want to format (EnglishProductName) and click on the toggle under “Background Colour”:

Step 2: Set the rules

In this example, we want the sum of the SalesAmount that is less than €20,000 to have a red colour and everything above €50,000 to have a green colour. Products for which there has been no Sales, we want to give them a white colour.

Select the value “Rules” under “Format By”. And under “Based on field” the “SalesAmount”. If the Default Summization is set to Sum, Summzarization will immediately show you the correct aggregation form “Sum”.

Then you can create the rules:

The end result is as follows:

Case study 2: Apply conditional formatting based on variable target values

In this scenario we want to give a red colour if a product has not reached the sales target and a green colour if it has. In the data model a separate table is available for this:

Step 1: Create a Measure that defines the Colour labels based on logic

We create a Measure that assigns a label ‘green’ if the sum of the sales amount is greater than the sum of the target. If this is not the case, a label “red” is written. To make sure that empty values are not Coloured, you can optionally add an IF statement that checks whether there are Sales for a certain product category. The DAX formula is as follows:

Measure Colourswitch SalesAmount Vs Target =
IF (
NOT ( ISBLANK ( SUM ( FactInternetSales[SalesAmount] ) ) );
SWITCH (
TRUE ();
SUM ( FactInternetSales[SalesAmount] )
> SUM ( ProductSalesTarget[Target SalesAmount] ); “green”;
“red”
)
)

Then test whether the Measure gives the desired output by adding it to the table or matrix.

Step 2: Set the Conditional Formatting for the desired column

The Conditional Formatting window appears. If this is not visible, first click on ‘Advanced Controls’.

Under “Format by” select “Field value” and under “Based on field” select the name of the Measure (Measure Colourswitch SalesAmount Vs Target) under. Click on Ok.

The end result is then:

You can, of course, omit the column in which the measure is displayed if you are going to offer the report to others so that they will only see the column with the colours.

Case 3: Conditional formatting of a background for a Card visual

In this example we give the background color of a Card Visual a certain color based on conditional formatting. You can also use the same method to colour the data label, for example.

Step 1: Turn on background Colour and go to the Conditional Formatting screen

Select the Card Visual and go under Visualizations Pane to the Background tab. Turn it on.

Move your mouse over the option Colour and you will see three vertical dots on the right. Click on them and then click on “Conditional formatting”. The conditional formatting window appears:

Step 2: Setting up the rules

Next, under Format By, select Rules. Under “Based on Field”, choose “Sales Amount”. In the example below, the background colour turns red if the turnover is between 0 and 20,000 and green if the values are higher than 20,001.

The end result is..:

What do you think?

Does Power BI have all the options for conditional formatting? Or do you want to have more features? Put your thoughts in the comments below!