With lots of data rows, it becomes difficult to understand data and interpret it easily. Conditional formatting is a method of highlighting certain rows of data in the table. It adds colors to certain cells of the table, making it look less boring. There are a few ways to perform conditional formatting in Power BI desktop:
- Conditional Formatting using Background Color.
- Conditional Formatting using Font Color.
- Conditional Formatting using Data Bars.
- Conditional Formatting using Icon.
- Conditional Formatting by Rule.
- Conditional Formatting by another column or measure.
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.
If you’re new to the concept of fact tables and dimension tables, here’s a blog to get started:
https://powerbitraining.com.au/what-is-a-data-model-in-power-bi/
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.
Now that a table with the required data has been created, now we apply conditional formatting to this table. For the images in this blog post, COGS has been conditionally formatted using the option of conditional formatting using color and Profit has been conditionally formatted using the option of conditional formatting using Font color. We will format the revenue column using the data bars as the conditional formatting option and later add icons to the Profit column.
Conditional Formatting Using Data Bars
In this section, we will walk through a use data bars to conditionally format a column.
To use conditional Formatting using Data Bars:
1. In the values section, click on the arrow on the right of Revenue.
2. Click on Conditional Formatting.
3. Click on Data Bars.
A pop-up menu appears.
There are various options in this pop-up menu which can be tuned according to your requirements.
4. Click OK.
Data bars appear at the back of each value in the Total Spend column.
Conditional Formatting Using Icon
In a recent update, Power BI introduced an option of showing unichar characters to conditionally format information.
To use conditional formatting using icon:
- In the Values section, click on the arrow at the right of Profit.
- Click on Conditional Formatting.
- Click on Icon.
A pop-up menu appears where Power BI intelligently detects the categories. We will use default categories and default icons for now.
4. Click OK.
Symbols which are appropriately colored instantly make the relevant information more prominent.
Conclusion:
Conditional formatting in Power BI desktop is a feature that is used to make some rows of data in a table visual more prominent then the others. There are various in which this can be done such as conditional formatting using background color, conditional formatting using font, conditional formatting using data bars, conditional formatting using icons, conditional formatting using rules and conditional formatting using another column or measure. In this blog post, the option of conditional formatting using data bars and icons has been discussed.
Learning Power BI from Scratch? Get a complete, in-depth introduction to Microsoft Power BI tool by attending our Power BI Basic Training Course.
More Checkouts for Power BI Training in Australia
