Power BI -Conditional Formatting-How to?

Table of Contents

Introduction

Conditional formatting is a feature in power BI and Excel, that allows you to apply formatting to cells or data based on specific conditions or criteria. The formatting changes are automatically applied when the data meets the specified conditions. It is mostly used in tables and matrix.

Prerequisite : Power Bi

Types Of Conditional Formatting

There are multiple ways by which you can conditional format in power Bi like –

Background Color
Font color
Data bars
Icons
Web URL

Where can i find conditional formatting option in Power BI ?

After creating a table/matrix with desired fields, the user can click on the field(s) where he wants to apply the conditional formatting and select one of the options ( background color, font color, etc.)

Background Color Conditional Formatting

First Select either table or matrix visual.

Drag and drop all the desired fields into the columns window

Go to one of fields and right click for options, go to conditional formatting and select background color

This window or dialog box will pop up with multiple options, you can click on ‘ok’ and let the default options apply basic conditional formatting or you change and tweak different options.
Note: In the picture below , we have numbered the options so we can explain it to you further.

Below we will be explaining the options

1 . Format Style

Format style has 3 options-
1) Gradient is the defualt one
2) Rules – In this option user can put conditions (eg. >, <, etc)

3) Field Value – This option helps you in selecting field to base conditional formatting on

2 . Apply To

This option helps user in selecting where the conditional formatting will be applied. In other words, it is asking user that where should power bi apply color gradient. It is not advised to use this option with totals, unless you are comparing totals too.

3. What field should we base this on?

Here you can select the data fields that will be used as a base for conditional formatting.

4. How should we format empty values

Here you can select how the empty values will be treated. Default is ‘As zero’ .

5 . Minimum

Gradient conditional formatting is based on value range that will be converted into color range. The default option is ‘Lowest value’ , in this option power bi automatically detects the lowest value of the user selected data field.
If you select Custom, you can type in the desired value.

6. Maximum

It works same like minimum option. Power Bi will find the maximum value by itself.

7. Add a middle color

You can pick a median value also.

After enabling the middle value option , your gradient will look like the below image

Font Color – Conditional Formatting

Second type of conditional formatting is font color.

It is similar to ‘Background color’ type of conditional formatting, the only difference is that instead of coloring the background, it colors the text. You can the quantity column below for example and comepare it with ‘All_transaction’ column where background color conditional formatting is applied.

Data bars – Conditional Formatting

In this type of conditional formatting, the size of the bar is dependent on the magnitute of value.

Below are the options that are available in Data bars conditional formatting. They are quite easy to understand if you have read about ‘Background color’ conditional formatting above.

options are pretty self-explanatory.

Here how data bars look

Icons – Conditional Formatting

Power Bi user can also use Icons for conditional formatting. You have to specify the icnon sets and rules for it to work. The default rules divide the values into three parts using percentage, although user can also use the ‘Value’ option instead of percentage. It is quite simple to use.