Recently, my colleague worked on a Power BI report where the requirement was to change the color of a bar in a bar chart based on its value. We researched and found that we could achieve this by using conditional formatting in the Power BI bar chart. And there are three methods to do it. Let me explain each method with examples.
Conditional Formatting in Power BI Bar Chart
As we know, Conditional formatting in Power BI is a feature that allows us to format visuals based on conditions or rules dynamically.
There are 3 different ways to apply conditional formatting:
- Gradient/Color Scale
- Rule
- Field value
By using these 3 methods, we will see how to format the colors of bars in the bar chart conditionally.
Apply Conditional Formatting in Power BI Bar Chart using Gradient/Color Scale
In this example, I use the sales financials data table (Financial sample.xlsx). You can create a similar Excel file and then follow the steps below.
Using this data, I create a bar chart that displays sales based on segment.
Based on the sales value, we will change the color gradient of the ‘Sum of Sales’ value in the Bar chart.
Three parameters—the lowest value, the middle value, and the highest value—will determine the gradient.
Follow the steps below to apply conditional formatting on the Power BI bar chart based on gradient.
1. First, Select the bar chart, open the Format pane, and expand the Bars section. Then click on the fx in the color section.
2. The Conditional Formatting window will open. Here, select the Format style as a Gradient. Select the Sales field in ‘What field should we base this on, ‘and select Sum as Summarization. Next, ‘How should we format empty values‘ as ‘As zero‘.
3. Then, in Minimum value, select Custom from the dropdown and enter the value as 10,000,000. Select the color. For the Maximum value select Custom from the dropdown and enter the value as 25,000,000. Also, you can enable the middle value by checking the box of Add a middle color. Then click on OK. You can set the value as per your requirements.
4. Now, you can see the color formatting is applied in the Power BI bar chart.
This is the first method to apply condition formatting in a bar chart.
Apply Conditional Formatting in the Power BI Bar Chart using the Rules
In the first method, we only added three parameters, where we added value, but here, we can add more than that. Let’s see how I do.
Here, we use the same bar chart, which shows sales based on segment.
1. Similarly, select the bar chart, open the Format pane, and expand the Bars section. Then click on the fx in the color section.
2. Now, the Conditional formatting window will open. Here, select the Format style as a Rule. Select the Sales field in ‘What field should we base this on, ‘and select Sum as Summarization.
3. Next, we set our rule. To add a new rule, click the +New rule button. I added the rules below, which you can add based on your requirements.
- If the value >= 0 [Number] and < 10000000[Number], then choose the color.
- If the value >= 10000000 [Number] and < 20000000[Number], then choose the color.
- If the value >= 20000000 [Number] and < 30000000[Number], then choose the color.
- If the value >= 30000000 [Number] and < 50000000[Number], then choose the color.
- If the value >= 50000000 [Number] and < Max[Number], then choose the color.
- Then click on OK.
4. Check the below screenshot. After applying conditional formatting, our bar chart looks like this.
Apply Conditional Formatting in the Power BI Bar Chart using the Field Value
Here, the field value is nothing but a measure we create. Inside the measure, we define a condition that changes the color based on whether it is met. Let’s see how I do.
Here, we use the same bar chart, which shows sales based on segment.
1. Now, we need to create the measure. To do this, click on the “New measure“. Then, write the below measure in the formula box:
SalesColor = SWITCH(TRUE(),
SUM(financials[ Sales]) <= 20000000, "Red",
SUM('financials'[ Sales]) <= 1000000000, "Yellow",
"Green")
This measure states that:
- If the sales amount is less than 20000000, then the bar color will be red.
- Else if sales amount is less than 1000000000, then the bar color yellow.
- Otherwise green
2. Select the bar chart, open the Format pane, and expand the Bars section. Then click on the fx in the color section.
3. Now, the Conditional Formatting window will open. Here, select the Format style as Field value. Then, select the measure (e.g., SalesColor) from the table under “What field should we base this on.” Finally, click OK.
4. Now, you can see that conditional formatting is applied to the bar chart in Power BI.
These are the three ways we can apply conditional formatting to a Power BI bar chart. I hope you understand. As a bonus, conditional formatting allows us to change the bar color and the chart background color, border color, shadow color, text color, gridlines, title background color, and more.
You may also like:
- Show Percentage in a Power BI Bar Chart Data Label
- Convert Base64 to PDF Using Power BI
- Show Zero Values in Power BI Bar Chart
- Power BI Bar Chart Rounded Corners
After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.
