I recently worked on a report where I needed to change the color of a bar in a stacked bar chart based on its value.
Would you like to learn how to apply conditional formatting in the Power BI bar chart?
In this Power BI tutorial, we will cover how to apply conditional formatting in the Power BI bar chart.
Conditional formatting in Power BI bar charts allows for the customization of bar colors, text, borders, gridlines, and more based on specified conditions or criteria.
Power BI conditional formatting 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 conditionally format the colors of bars in the bar chart.
Power BI Conditional formatting bar chart [Gradient/color scale]
Here, we will see how to apply conditional formatting to a bar chart based on the gradient in Power BI Desktop.
For example, we will use the Financial Excel sheet data to create a Stacked bar chart in Power BI Desktop. The stacked bar chart will display the Sales based on Segment.
Based on the sales value, we will change the color gradient of the ‘Sum of Sales’ value or data label font color in the Stacked Bar chart.
The gradient will be determined by three parameters: the lowest value, the middle value, and the highest value.
Suppose the minimum sales amount is 10,000,000 and the maximum amount is 25,000,000. In that case, the font color labels will transition from light yellow for lower sales to dark yellow for higher sales, with shades in between reflecting varying sales amounts.
Follow the steps below to apply conditional formatting on the Power BI Stacked bar chart based on gradient.
- Open Power BI Desktop, and then click on Get data -> select Excel Workbook.
- Then select the Excel workbook from your local system; in the Navigator window, select the table and click on the Load button.
- Select the Stacked bar chart from the visualization pane to create a bar chart in Power BI Desktop Canvas.
- To display the sales based on segment, drag and drop the Sales and Segment columns to the X and Y axis, respectively.
- Next, click on the Format icon in the Visualization pane, then expand the Bar section.
- Click on the fx in the color section.
- 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‘.
- 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.
Now you can see the color formatting is applied to the bar chart in Power BI.
Power BI Conditional formatting bar chart [Rules]
Here, we will see how to apply conditional formatting based on rules in the Power BI bar chart.
We’ll implement conditional formatting rules for the bar color in the Power BI Stacked bar chart, which displays the Sum of sales by segment.
Here, we will apply the condition formatting rule to change the bar color dynamically in the Power BI Stacked bar chart. The rule is:
- If the Total Sales amount is greater than 0 and less than 20000000, then the bar color is light pink.
- Else if the Total Sales amount is greater than 20000000 and less than 1000000000, then the bar color is dark pink.
Now, let’s see how we can apply the rule to the bar color in the Stacked bar chart.
- In Power BI Desktop, select the Stacked bar chart from the canvas.
- Then click on the Format pane icon.
- Next, click on the Format icon in the Visualization pane, then expand the Bar section.
- Click on the fx in the color section.
- 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.
- Next, If the value >0 [Number] and less than 20000000[Number], then choose the color.
- Then click on the +New rule button.
- If the value >20000000 [Number] and less than 1000000000, then choose the color
- Then click on OK.
Now you can see the color formatting is applied to the bar chart in Power BI.
Power BI Conditional formatting bar chart [Field value]
Here, we will see how to apply conditional formatting on the Power BI Stacked bar chart based on a field value.
For example, we will use the data and create a stacked bar chart in Power BI Desktop, which shows information related to Sales based on Segment.
To format the bar color in the Power BI bar chart based on the field value. We need to create a measure; for 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
Now, let’s see how we can apply the field value to the bar color in the Stacked bar chart.
- In Power BI Desktop, select the Stacked bar chart from the canvas.
- Then click on the Format pane icon.
- Next, click on the Format icon in the Visualization pane, then expand the Bar section.
- Click on the fx in the color section.
- Now, the Conditional formatting window will open.
- Here, select the Format style as a Field value.
- Select the Measure from the table under ‘What field should we base this on‘
- Then click on OK.
Now, you can see that conditional formatting is applied to the bar chart in Power BI.
Conclusion
In this Power BI tutorial, we saw how to apply conditional formatting to a Power BI bar chart to change the bar colors.
You may also like:
- Conditional Formatting in Power BI Line Chart
- Power BI Pie Chart Conditional Formatting
- Power BI Bar Chart Rounded Corners
After working for more than 15 years in Microsoft technologies like SharePoint, Office 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 (9 times). I have also worked in companies like HP, TCS, KPIT, etc.