Conditional Formatting In Power BI with Rules

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

Conditional Formatting In Power BI with Rules
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 (Conditional Formatting With Rules) 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. Conditional Formatting In Power BI with Rules

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.

Conditional Formatting In Power BI with Rules

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.
Conditional Formatting In Power BI with Rules

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/ )

Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.

Navneeth Nagrajan is a Technical Consultant at Avanade Australia focusing on implementation, pre-sales and support of Microsoft Dynamics 365 for Operations/ Microsoft Dynamics Ax ERP. Before Avanade, Navneeth worked as a Solutions Consultant at LM WindPower focusing on implementation and support of Microsoft Dynamics Ax Enterprise Resource Planning solution. Prior to LM WindPower, he worked as a Lead Consultant for ITC Infotech India Limited focusing on Enterprise Solutions like Microsoft Dynamics AX & Microsoft Dynamics CRM. He has worked for Microsoft as a Partner Technical Consultant. His primary focus area is Microsoft Dynamics AX and he specializes on Trade & Logistics, CRM, Service Management and Project Management functionalities. His secondary focus area is Microsoft Dynamics CRM and he specializes on Sales Management, Marketing Management and Service Management sections. He has successfully assisted more than 100 Microsoft partners and customers. At Microsoft, He has successfully written and published papers on '​'​ Needs for Microsoft Dynamics CRM in the Education Sector''. Prior to Microsoft, he has worked as a Functional Consultant and has implemented Microsoft Dynamics AX in the Manufacturing and Services Segment. He has also worked as a Business Development Executive implementing Document Management Systems for Security Broking and Manufacturing Organizations.