Conditional formatting is type of feature which are used to highlight cells of a data set in different type of colors based of certain or different conditions, this feature is used in both ms excel and power bi, there is a catch for conditional formatting in power bi and it is that we can manage it in the value area of the fields.

Conditional Formatting in Power BI

If you think Power BI is just limited to visualization and charts then you will spend a considerable amount of time figuring out how the below colors or in cell bars have been applied. This is not a bar chart or some other visualization rather it is conditional formatting applied to the table visually. Let me tell you frankly I have spent a considerable amount of time figuring out this, so here you go this article will explain in detail the conditional formatting in Power BI.

To apply Power BI Conditional Formatting you need data to work with, so you can download the excel workbook template from the below link which is used for this example.

You can download this Power BI Conditional Formatting Excel Template here – Power BI Conditional Formatting Excel Template

How to Apply Conditional Formatting in Power BI with Examples

Below is the table I have already created in Power BI report tab.

For the table, I have drag and dropped “Sale Value, COGS, Discounts, and Gross Profit” columns from the table.

To apply conditional formatting in Power BI to this table under the field section of the table click on the “drop-down” icon of “Sale Value”.

When you click on that down arrow you will see below options for this column.

From the above list just hover on “Conditional Formatting” and it will further show many conditional formatting options in Power BI.

As you can see above we have four kinds of conditional formatting types, “Background Color, Font Color, Data Bars, and Icons”.

Popular Course in this category

Power BI Training Certification4.5 (521 ratings) 6 Online Courses | 29+ Hours | Verifiable Certificate of Completion | Lifetime Access

View Course

We will see one by one for all the four columns of our table.

Example #1 – Using Background Color

First, we will apply the background color for the “Sale Value” column. Once again click on the down arrow of “Sale Value” column and choose Conditional Formatting and under this choose “Background Color”.

This will opens up below the formatting window.

Under “Format By” drop-down list we can choose various other types for this category of conditional formatting but as of leaving it as “Color Scale” only.

Then make sure “Based on Field” is the “Sum of Sale”, “Summarization” as “Sum”, and “Default formatting” as “Zero”.

Now for “minimum values” it has already chosen “Red” color and for “maximum” value it has chosen light “blue” color.

If you want to have one more color for middle values then check the box “Diverging” to get the middle-value section.

Now click on “Ok” and we will have background color as follows.

Example #2 – Using Font Color

For “COGS” column we will apply “Font Color” conditional formatting. From conditional formatting choose “Font Color”.

This will opens up below window.

This is similar to the one we have seen above but this time, as usual, you choose your interesting colors, I have chosen below color.

After choosing the colors click on “Ok” and we will have below colors for “COGS” numbers in the table.

Example #3 – Using Data Bars

Data bars look like an “in-cell” chart based on the value of the cell value. For the “Discounts” column we will apply “data bars”.
Click on the down arrow of “Data Bars” and choose Conditional Formatting”. Under this category of “Conditional Formatting” choose “Data Bars”.

This will opens up below the “Data Bars” conditional formatting window.

In this category, we can choose whether we want to show only “Bars” or we want to show bars along with numbers.

Since we need to see numbers along with bars, let’s not check this box. Next, we can choose colors for “Minimum” & “Maximum” values.

Choose the colors as you would like to and click on Ok we will have below in cell charts or bars for “Discount” column.

Example #4 – Using Icon Sets

One final category under conditional formatting is “Icon Sets”. These are icons to show against numbers based on the rules specified,
Click on the down arrow of “Profit” and choose conditional formatting.