How to Apply Conditional Formatting in Power BI Bar Chart?

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.

How to conditional formatting bar chart in power bi

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.

See also  How to remove items from a collection in Power Apps [With Examples]

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.
Microsoft power bi bar chart color by category
  • Then select the Excel workbook from your local system; in the Navigator window, select the table and click on the Load button.
power bi stacked bar chart color based on value
  • Select the Stacked bar chart from the visualization pane to create a bar chart in Power BI Desktop Canvas.
 conditional formatting bar chart in power bi
  • To display the sales based on segment, drag and drop the Sales and Segment columns to the X and Y axis, respectively.
power bi conditional formatting bar chart
  • Next, click on the Format icon in the Visualization pane, then expand the Bar section.
  • Click on the fx in the color section.
Microsoft power bi conditional formatting bar chart
  • 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.
Microsoft power bi conditional formatting Stacked bar chart

Now you can see the color formatting is applied to the bar chart in Power BI.

power bi conditional formatting Stacked bar chart

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.

power bi conditional formatting bar chart

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.
See also  How to Check if a field exists in Power Automate?

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.
Microsoft power bi conditional formatting bar chart
  • 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.
power bi conditional formatting Stacked bar chart based on rule

Now you can see the color formatting is applied to the bar chart in Power BI.

power bi conditional formatting bar chart based on rule

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.

power bi conditional formatting bar chart

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")
power bi bar chart color based on value

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
See also  Power Automate Create a CSV Table [7 Examples]

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.
Microsoft power bi conditional formatting bar chart
  • 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.
power bi bar chart color by category

Now, you can see that conditional formatting is applied to the bar chart in Power BI.

power bi stacked bar chart color by category

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:

>