Conditional formatting is an excellent feature to change a cell’s appearance and helps to distinguish visually. Power BI allows you to use percentage-based conditional formatting to make the table visuals more attractive and understandable.
However, some users reported that they have problems with conditional formatting on percentage values. If you are one of them, don’t worry.
Here, you will find a quick and easy solution on how to solve Power BI conditional formatting not working with percentages.
Let’s dive in.
How to Fix Conditional Formatting with Percentage Not Working in Power BI
Power BI conditional formatting helps to interpret a report table easily at a glance. If the table contains columns with the percentage data type, you can change the cells’ colors based on the percentage values.
Sometimes, the color doesn’t change according to the conditions. Why?
A wrong threshold value can lead to conditional formatting with the percentage in Power BI not functioning. In addition, wrong data types and conflicted formatting rules can also cause the formatting rules not to work.
Here are the steps to fix conditional formatting with the percentage is not working in Power BI:
1. Use Number Instead of Percent in Conditional Formatting Settings
When you have trouble with conditional formatting with percentages, you should check the rule settings first. If you use Percent to set the threshold, change it to the Number.
Here’s the process for using the number rule in conditional formatting settings:
- Open your report in the Power BI desktop app.
- Select Table type from the Visualizations pane.
- Right-click on the desired columns, hover the mouse on the Conditional formatting, and select the Background color.
- Pick Rules from the Format style.
- Select the Number for all the rules in the threshold parameter.
- Set the limit values & colors and hit OK.
If the column’s data type is set to the Percentage, you must enter the limit numbers in the rules as decimals representing the actual values. For example, 1.5 instead of 150. And you are good to go.
Keep reading if the issue still persists.
2. Check the Data Type
As mentioned earlier, the data type can affect the limit number you set in the conditional formatting settings.
If the Data type is set to Text, you must enter the table’s actual value to format according to the condition. So, for StoreNumber in the following image, where the data type is Text, you have to enter the actual value to format the column.
When the Data type is set to Percentage, divide the report table’s value by 100 to get the decimal value and use that in the rules section.
So, if you want to set a condition with equal or greater than 100 and less than 150, you must type 1 and 1.5 in the range box.
You can verify the data type from the power query editor. To check the data type in Power BI, open a report > select Transform data from the Home tab > choose the Transform tab, and there you will find the data type in Any column group.
3. Verify Data Values in the Columns
Before setting conditions in the formatting rules, you should check the actual values in the report column. You must enter the limit value within the expected range.
That being said, if negative percentage values exist in the report, you must enter the threshold value according to that.
If you want to set a condition for the negative value, you must use the negative sign. Otherwise, the conditional formatting won’t work for those cells.
4. Adjust Value Limit
Humans are error-prone. Even though you have double-checked the threshold value, you can skip the wrong one due to thinking it is right.
I often mistakenly press an extra zero after a point when the value should start exactly after the point, like 0.05 instead of 0.5. It can also happen to you when dealing with a large report.
This happens frequently, especially when you are dealing with the percentage data type, as they are related to the decimal values. If you make any mistakes, the formatting won’t function and will not display the corrected color.
So, ensure you enter the correct value and adjust the value limit if required.
5. Remove Other Formatting Rules
If the table has other conditional formatting rules, consider removing them. It is unusual, but one formatting rule can sometimes conflict with the other. If that’s the case, the conditional formatting in Power BI doesn’t respond.
Clearing existing formatting rules and starting from scratch can help you achieve the desired formatting.
To delete conditional formatting in Power BI, open the report with the table visual > right-click on the column name > select Remove conditional formatting and choose All.
Sometimes, the outdated Power BI app can cause issues with various functions. If you are using an obsolete version, consider updating the app. You can visit the Microsoft site to download and install the latest version.
Alternatively, you can also update the app from the Microsoft Store.
If the Power BI dataset refresh is not working, your report can have obsolete contents, which can cause the conditional formatting to not function due to conflicted data. So, you must ensure the report is updated.
Final Thought
If you have trouble with conditional formatting not functioning with percentages in Power BI, this article will help you fix the issue. For any further queries, please leave a comment.
Have a nice report with colorful cells!
