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’.
 
Conditional Formatting With Background Color Of Cell In Power BI
 
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.
 
Conditional Formatting With Background Color Of Cell In Power BI
 
Step 2
 
Now go to salary column and right click on it. Click on ‘Conditional Formatting’ and further click on ‘Background Color’.
 
Conditional Formatting With Background Color Of Cell In Power BI
 
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
Conditional Formatting With Background Color Of Cell In Power BI
 

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.
 
Conditional Formatting With Background Color Of Cell In Power BI 
 
‘Sal’ column will be shown as below image.
 
Conditional Formatting With Background Color Of Cell In Power BI 
 

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.
 
Conditional Formatting With Background Color Of Cell In Power BI 
 
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.
 
Conditional Formatting With Background Color Of Cell In Power BI 
 

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’. 
 
Conditional Formatting With Background Color Of Cell In Power BI 
 
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")
 
Conditional Formatting With Background Color Of Cell In Power BI 
 
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’.
 
Conditional Formatting With Background Color Of Cell In Power BI
 
Conditional Formatting With Background Color Of Cell In Power BI 
 

Summary

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