Conditional Formatting With Background Color Of Cell In Power BI

Introduction

 

Conditional formatting is a very interesting feature of Power BI. You can specify the column data with different color by background formatting and font color formatting.

 

There are five types of conditional formatting available in Power BI:  Background color, Font Color, Data bars, Icons and Web URL.

 

Here in this article you will see the conditional formatting by background color. To perform conditional formatting, Use Table or Matrix visualization.

 

To open the Conditional formatting, Select the column on which the formatting will be applied and right click on it and then click on ‘Conditional Formatting’ and click on ‘Background Color’.

 

 

Here I am going to show you background color formatting on Salary column. My task is to show salary in different colors as per salary scale. For example- where salary is less then 15000, cell background should be red, from 15000 to 30000 is yellow and greater than 30000 should be green.

 

Salary	Cell Background Color
<15000	Red
15000-30000	Yellow
>30000	Green

 

See the below steps to perform this task.

 

Step 1

 

I have imported an Excel sheet with employee data. To import excel data check ‘https://www.c-sharpcorner.com/article/import-excel-data-in-power-bi/’.

 

I am using table visualization and select Ename, empno, deptno, job ad salary column, you can use matrix visualization also.

 

 

Step 2

 

Now go to salary column and right click on it. Click on ‘Conditional Formatting’ and further click on ‘Background Color’.

 

 

A new window will be opened. Here you get an option ‘Format by’. Once you click on drop down menu you will get three options.

1. Color scale
2. Rules
3. Field value

 

Color scale

 

First option does not match with our task but still I am going to show you how it works. Select the color scale option and select ‘Values only’ in apply to section and another field as shown in image. You can choose any color as per your choice.

 

Click on ‘Ok’ to apply formatting.

 

 

 

‘Sal’ column will be shown as below image.

 

 

 

Rule

 

Select ‘Rule’ as ‘Format by’ and apply rule as per our condition. As we have to apply three conditions and default there will be a single one, so click on ‘New Rule’ to add new rule.

 

See the below image.

 

 

 

You can move the condition by clicking on the up & down arrows of every rule. Click on ‘Ok’ to apply these rules. Your column will be shown as below image.

 

 

 

Field value

 

To select field value option, there should be a column that must have color value like red, green etc. as we don’t have any such column in our table so we will first add that field in our table according to our condition.

 

To add such field go to modeling tab and click on ‘New column’. 

 

 

 

A new column will be added. I am giving the name of that column as ‘sal_color’ and applied the below formula.

 

sal_color = SWITCH(TRUE(), Employee[sal]<=15000, "red", Employee[sal]<=30000, "yellow", Employee[sal]>30000, "green")

 

 

 

Now go to ‘sal’ column and open ‘conditional formatting’ and select format by ‘Field value’ and select ‘sal_color’ as ‘based on field. Now click on ‘Ok’.

 

 

 

 

Summary

 

This is the way of background color formatting of a cell. Hope you understand these simple steps. Thanks for reading.