Conditional Formatting In Power Bi Stacked Column Chart

Do you want to know how to apply conditional formatting to a Stacked column chart in Power BI?

In this Power BI tutorial, we will see how to apply conditional formatting in Power BI Stacked column chart.

Conditional formatting in Power BI Stacked Column chart allows for the customization of column colors, text, borders, gridlines, and more based on specified conditions or criteria.

Power BI Stacked column chart conditional formatting

Here, we will see how to apply conditional formatting in the Power BI stacked column chart.

There are 3 format styles to apply conditional formatting in Power BI.

  • Color Scale/ Gradient
  • Rule
  • Field value

By using these 3 methods, we will see how to conditionally format the colors of columns in the Stacked column chart.

Conditional formatting stacked column chart Power BI using Gradient

Here, we will see how to apply conditional formatting to a Stacked column chart based on the gradient in Power BI Desktop.

For example, we will use the Financial Excel sheet data to create a Stacked column chart in Power BI Desktop. The stacked column chart will display the Sales based on Segment.

conditional formatting stacked bar chart power bi

Based on the sales value, we will change the color gradient of the columns in the Stacked column chart.

The gradient will be defined by three parameters: the lowest value, the middle value, and the highest value.

Suppose the lowest sales amount is 10,000,000 and the maximum amount is 22,000,000. In that case, the column color 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 add text input to Power Apps collection?

Follow the steps below to apply conditional formatting on the Power BI Stacked column chart based on gradient.

  • Open Power BI Desktop, and then click on Get data -> select Excel Workbook or you can select the Excel Workbook from the ribbon.
conditional formatting stacked bar chart in power bi
  • Then select the Excel workbook from your local system; in the Navigator window, select the table and click on the Load button.
conditional formatting stacked bar chart in Microsoft power bi
  • Select the Stacked column chart from the visualization pane to create a column chart in Power BI Desktop Canvas.
conditional formatting stacked bar chart in the Microsoft power bi

To show the sales based on segment, drag and drop the Sales and Segment columns to the X and Y axis, respectively in the field pane.

conditional formatting stacked column chart in the Microsoft power bi
  • Next, click on the Format icon in the Visualization pane, then expand the Columns section.
  • Click on the fx in the color section.
conditional formatting stacked column chart in the power bi
  • The Conditional Formatting window will open. Provide the below information:
  • Select the Format style as a Gradient.
  • Choose 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 22,000,000. Select the color.
  • Also, you can enable the middle value by checking the box of Add a middle color. Then click on OK.
conditional formatting stacked column chart in power bi

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

conditional formatting stacked column chart power bi

Conditional formatting stacked column chart Power BI using Rules

Here we will see how to format columns color in Power BI Stacked column chart conditionally using rules.

See also  Power Bi count function - DAX Examples

For example, we will implement conditional formatting to the columns of the Stacked Column chart [shows sales based on Segment] based on the rule.

conditional formatting stacked column chart power bi based on rule

To apply conditional formatting in the above-stacked column chart, change the color of the column based on the rule below.

  • If the Total Sales amount is greater than 0 and less than 22000000, then choose the color
  • Else if the Total Sales amount is greater than 22000000 and less than 1000000000, then choose the color.

Follow the below steps to apply conditional formatting on the stacked column chart.

  • In Power BI Desktop, select the Stacked column chart from the Visualization pane.
  • Then click on the Format pane and expand the column selection.
  • Then, select the fx in the color section to apply conditional formatting.
conditional formatting stacked column chart in power bi based on rule
  • The conditional formatting window will open; provide the information below:
    • 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 22000000[Number], then choose the color.
    • Then click on the +New rule button.
    • If the value is > 22000000 [Number] and less than 1000000000, then choose the color
    • Then click on OK.
conditional formatting stacked column chart based on rule

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

conditional formatting stacked column chart based on rule in Power BI

Conditional formatting stacked column chart Power BI using Field values

Here we will see how to conditionally format columns color in Power BI Stacked column chart using field values.

For instance, we will use the data and create a stacked column chart in Power BI Desktop, which shows information related to Sales based on Segment.

power bi stacked column chart color based on value

To format the column color in the Power BI column chart based on the field value. We need to create a measure, so click on the New measure. Then, write the below measure in the formula box:

Column color = SWITCH(TRUE(),
SUM(financials[ Sales]) <= 22000000, "	#FFD580",
SUM('financials'[ Sales]) <= 1000000000, "Dark Orange",
"Green")
microsoft power bi stacked column chart color based on value

This measure states that

  • If the sales value is less than 22000000, then the column color will be Light orange.
  • Else if the sales value is less than 1000000000, then the column color Dark Orange.
  • Otherwise green.
See also  How to Work With Power Apps Dropdown Blank Values

Follow the below steps to use this measure in conditional formatting the Stacked column chart.

  • In Power BI Desktop, select the Stacked column chart from the Visualization pane.
  • Then click on the Format pane icon and then expand the Column section.
  • Click on the fx in the color section.
conditional formatting stacked column chart in power bi based on field value
  • Now, the Conditional formatting window will open in Power BI Desktop. Provide the information below
    • 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 stacked column chart conditional formatting
  • Now you can see the conditional formatting is applied to the columns of the Power BI Stacked column chart.
microsoft power bi stacked column chart conditional formatting

This is how to apply conditional formatting in the Power BI Stacked column chart using field values.

Conclusion

In this Power BI tutorial, we saw how to conditionally format the Stacked column chart in Power BI Desktop.

You may also like:

>