Power BI Conditional Formatting
What is Conditional Formatting?
Conditional formatting is a rule which will automatically apply a format to a range of cells in a table. So for example, if you have a table that contains sales values, you could apply a rule to color any cell with a value over £5,000. Or you could set a gradient color to the entire table so the bigger the value, the darker the shade. It is a great way to easily identify the high and low values in a large range of numerical values. Both Excel and Power BI offer conditional formatting as a feature:
Key Learning Points
- Conditional formatting is a way to automatically color or format numbers based on their values
- Conditional formatting helps the reader identify high and low numbers more easily in a large table. It also is useful to help see patterns and trends
- In Power BI, conditional formatting is applied to the entire field on a particular visual
- You can set your own rules to color or format all the values over or under a certain amount
Power BI Conditional Formatting
In Power BI, conditional formatting is applied to the entire field in a visual. So for example, apply a background or font color to the Sales field in a table, or a rule for the Profit field in a card to color red if negative and green if positive.
How to Apply Power BI Conditional Formatting to a Format Background or Font Color
One of the most useful ways to use conditional formatting in Power BI is with a table or matrix visual. Power BI Table conditional formatting is very easy to set up. Simply follow the steps below:
- Create the table or matrix visual on the report page
- Use the Visualizations Pane/Format Visual to Expand Cell Elements on the Visual Section
- If your field contains several fields, make sure the correct field is selected as the Series (normally this would be your numeric field such as Sales or Profit)
- Turn on the Background color or Font color. The values in the table or matrix should now have gradient color based on their value.
- If you wish to customise the color, then click on the fx icon and change the color
How to Apply Power BI Conditional Formatting to a Color by Rules?
It is also possible to set your own rules for a visual in Power BI. Tables, Matrixes, Cards, and Column or Bar charts are just some of the visuals where you can do this.
For example, you could apply conditional formatting on a Power BI Bar chart to color only the bars which have a negative profit. Simply follow the steps below:
- Create the bar chart with the appropriate fields – the example below shows the total profit for each product
2. Use the Visualizations Pane/Format Visual to Expand Bars/Colors on the Visual Section
3. Click on the fx icon to set a rule
4. Change the Format Style to Rules and make sure you have the correct field and summarization – so for example, Sum of Profit and Sum. Click on the New Rule button to add in as many rules as you need
Conclusion
Conditional Formatting is a really useful way to make key numbers or patterns or trends in the data stand out. Try to use it as often as possible, especially in tables where it can be difficult to interpret a large amount of numbers. It will make it much easier for your audience to understand.
Enroll on our online data visualization course, and learn how to use other Power BI features to cleanse, visualize, share and present data effectively.