How to Do Conditional Formatting In Power BI with Rules – Font Color Scales

In PowerBI, conditional formatting was a capability that was included in the November 2017 update. In PowerBI, there is a feature summary update, where there are new features added to the PowerBI desktop, an integrated environment or a workspace used to build and design reports.

Conditional Formatting In Power BI with Rules

Conditional formatting is a capability that enables background color formatting or text formatting of cell values in a table or a matrix visual. With the November 2017 release, it is now possible to add rule-based color formatting, based on the values in our table or matrix visuals.

In this post, I will highlight the conditional formatting with rules capabilities of Microsoft PowerBI.
What is PowerBI?
PowerBi is a set of connectors, software services, and applications (on the cloud and on-premise) that can be used to combine unrelated or related sources of data into coherent, visually immersive and interactive insights.

Also Read: Power BI Desktop – Interview Questions

Sample Example (Conditional Formatting With Font Color Scales):
In this sample example, below I have used a simple table that is used to give us a breakdown of the Product Revenue, by category, with the following fields. The Product category is classified as Urban, Mix, and Rural. The first four fields in the fields list below are added to a hierarchy called ‘Products.’

Fields (In Table visual):

• Category (Under Products Hierarchy)
• Segment (Under Products Hierarchy)
• Manufacturer (Under Products Hierarchy)
• Product (Under Products Hierarchy)
• Revenue
• Units
• YTD Revenue

Fig 1 – Order of fields in the Products table

Rule:
The rule that is being applied here is as mentioned below:
For revenues that are greater than or equal to $0 and less than $1000, the values are highlighted in red. For revenues greater than $1000 and less than $100,000, the values are highlighted in green.

To apply conditional formatting, in the Visualisations Pane, under the Values property of the table, ensure that the fields are added, in the order, stated above under the fields section and as showcased in Fig 1. The order can be viewed under the Visualizations pane under the Values section.

In this example, we need to apply the conditional formatting rules on the Revenue field, so right click on the Revenue field or select the little arrow to the right of the field () and choose Conditional Formatting. Choose Font color scales or background color scales. In this case, I have decided Font color scales, so the numbers that show up in the Revenue column will be conditionally formatted in red or green, based on the conditions stated above. 

Fig 2 – Font Color Scales Property Under Conditional Formatting

In the Font Color Scales window, define the values after pushing the Add rule button in there and set the rules, as stated below. You can move the order of the fields and change the order in which those conditions can be executed. In this case, we have two independent If conditions. The name of the rule is ‘Revenue’ that is defined under Base values and also, ensure that the Color by rules is selected, to have color codes on the values under the Revenues column in the table.

Fig 3 – Font Color scale Window

Once these rules are defined, the output will be as shown below in Fig 4 – Font Colors in the Revenue Column.

Fig 4 – Font Colors in the Revenue Column

Note:  Ensure that the data type of the Revenue field is set to Currency.

Key Takeaways:

1.  Download the latest release of the PowerBi desktop (or the November Update release), to leverage the conditional formatting with rules capability. To download the PowerBi desktop, use the link below.
(PowerBI Desktop:  https://powerbi.microsoft.com/en-us/desktop/)

2.  For more information about the November 2017 release of PowerBI, refer to the link below.
(November Feature Summary Update:                                                                                                                                       https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-november-2017-feature-summary/ )