Power BI

Conditional Formatting by Color in Power BI

In our Power BI trainings in Sydney and Melbourne, we’re often asked questions like: What could add spice to a boring list of numbers such as table values? Or what could you do to make visuals look more appealing? Here’s the answer to all such questions put forward in Power Bi trainings in Sydney and Melbourne.

Conditional Formatting helps you make certain information more prominent. This makes visuals look more appealing. Using conditional formatting you can visualize data in a way which make the data values more distinguishable. Conditional Formatting saves time and resource by applying formatting rules to the entire column. In this blog post, two types of conditional formatting have been discussed:

  1. Conditional Formatting by Background color.
  2. Conditional Formatting by Font Color.

Getting Started

The data set used for this blog consists of two tables: a fact table (Sales_Table) and a dimension table (Product_Table). DAX measures have been created in a separate table following the best practice recommended by our experts in Power BI Training.

To Get Started:

1.Create a table All Measures for measure values.

2.Create a new measure Revenue according to the following DAX expression.

3.Create a new measure COGS according to the following DAX expression.

4.Create a new measure Profit according to the following DAX expression.

5.Click on the Table visual in the Visualization Pane.

6.Drag and drop the Product from Product_Table table to the Values.

7.Drag and drop the COGS from All Measures table to the Values.

8.Drag and drop the Revenue from All Measures table to the Values.

9.Drag and drop the Profit from All Measures table to the Values.

Conditional Formatting by Background Color

Background Color is one way to visually categorize your data.  This means coloring the background of the cell using rules or values.

To use Conditional Formatting by Background Color:

1.In the Values area, click on arrow at the right of COGS.

2.Click on Conditional Formatting.

3.Click on Background Color.

A pop-up menu appears.

4.Select the check box at the left of Diverging.

5.Click OK.

Notice that the  COGS column is colored based on the values. Lower values are colored “Red” and the higher values  

Removing Background Color Formatting

To Remove Background Color Formatting:

6.Click on COGS.

7.Click on Remove Conditional Formatting.

8.Click on Background Color.

Notice that all options except background color are greyed because they are not yet applied.

Conditional Formatting by Font Color

Now that we have learnt how to format the background of the cell, lets learn how to change the font color of the cell based on certain rules or values. Conditional Formatting can also be applied to alter the Font Color of the text values in the column.

To use Conditional Formatting by Font Color:

1.Click on the arrow at the right of Profit.

2.Click on Conditional Formatting.

3.Click on Font Color.

A pop-up menu appears.

4.Select the Check box at the left of Diverging.

5.Click OK.

Notice that each value in the Profit Column is colored. Red colored values donate low profit margin values whereas green values donate high profit margins.

Conclusion:

Conditional Formatting is a feature in Power BI that allows you to customize cells of table according to values or rules that you can define yourself. This feature comes in with different options such as conditional formatting using background color or font, Conditional formatting using data bars, Conditional formatting using DAX function, Conditional formatting using Data icon and conditional formatting using rules. In this blog post, conditional formatting using background color and font color have been discussed. 

Are you a data analyst and want to learn more about Power BI?

Why not sign up for Power BI training in Australia.  We provide our services in the following regions:

Back to list

Leave a Reply

Your email address will not be published. Required fields are marked *