How to Apply Conditional Formatting in Power BI Area Chart?

This Power BI tutorial will show how to apply conditional formatting in Power BI Area chart.

I recently needed to dynamically modify the color of a shaded area while working with an Area chart in a Power BI report. This can be achieved with conditional formatting.

Unfortunately, this functionality is currently unavailable in the Power BI Area chart. However, changing the color of a line marker in the Power BI Area chart is possible.

However, I’ll guide you on how to change the colors of the Power BI Area chart marker.

How to Apply Conditional Formatting in Power BI Area Chart

Here, we will see how to apply conditional formatting in Power BI Area chart markers.

For example, we will use the below sample Excel table to create an Area chart.

power bi area chart conditional formatting

We will display the COGS based on the Product in the Power BI Area chart. Since we can’t directly format the lines in an Area chart, we’ll either convert the Area chart to a Column chart or directly create a Column chart. Then, we’ll apply conditional formatting and convert it to an Area chart.

Let’s see how we can apply conditional formatting to the Power BI Area chart markers.

Apply Conditional formatting to the Power BI Area chart Line marker [Using Gradient]

  • Open Power BI Desktop, then click on Excel Workbook to load the Excel sheet.
Microsoft power bi area chart conditional formatting
  • Then, select the Excel sheet from your local system.
  • Next, in the Navigator window, select the table and click the Load button.
 area chart conditional formatting
  • Select the Stacked column chart from the visualization pane to create the Column chart.
  • Drag and drop the COGS and Product columns to the Y-axis and X-axis, respectively, to show the COGS based on the Product.
area chart conditional formatting gradient
  • Next, click on the Format pane, then expand the Column section.
  • Click on the fx in the color section to apply conditional formatting.
Power BI area chart conditional formatting gradient
  • It will open the Conditional Formatting window. Provide the information below to apply conditional formatting.
    • Here, select the Format style as a Gradient.
    • Select the COGS 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 15000000. Select the color.
    • For the Maximum value select Custom from the dropdown and enter the value as 20000000. Select the color
    • Also, you can enable the middle value by checking the box of Add a middle color. Then click on OK.
Power BI area chart conditional formatting by gradient
  • Now you can see the color formatting is applied to the column chart in Power BI.
Power BI area chart conditional formatting based on gradient
  • Select the Area chart from the Power BI visualization pane to convert the column chart to an Area chart.
  • Now, the Area chart marker is formatted based on the value.
Area chart conditional formatting by gradient

Apply Conditional formatting to the Power BI Area chart Line marker [Using Rule]

We will see here how to apply conditional formatting based on rules in the Power BI Area chart marker.

See also  How to set default value in Power Apps data card?

Based on the COGS by product, we’ll implement conditional formatting rules for the marker in the Power BI Area chart.

conditional formatting in area chart

Here, we will apply the condition formatting rule to change the color in the Power BI Area chart marker. The rule is:

  • If the COGS is greater than 0 and less than 15000000, then choose the marker color
  • Otherwise, if the Total COGS amount is greater than 15000000 and less than 45000000, then choose the marker color.

Now, let’s see how to apply the rule to the marker color in a Power BI Area chart.

  • In Power BI Desktop, select the Stacked column chart from the visualization pane to create the Column chart.
  • To show the COGS based on the Product, drag and drop the COGS and Product columns to the Y-axis and X-axis, respectively.
Area chart conditional formatting by rule
  • Next, click on the Format pane, then expand the Column section.
  • Click on the fx in the color section.
Power BI area chart conditional formatting gradient
  • Now, the Conditional formatting window will open.
  • Here, select the Format style as a Rule.
  • Select the COGS field in ‘What field should we base this on‘, and select Sum as Summarization.
  • Next, If the value >0 [Number] and less than 15000000[Number], then choose the color. Then click on the +New rule button.
  • If the value >15000000 [Number] and less than 20000000, then choose the color
  • Then click on Ok.
power bi area chart change color
  • Now, you can see the conditional formatting is applied to the column chart in Power BI.
Microsoft power bi area chart change color
  • To convert the column chart to an Area chart, select the Area chart from the visualization pane.
  • You can see the Area chart marker color changed based on color formatting.
power bi area chart change color by rule

This is how we can apply conditional formatting based on the rule in the Power BI Area chart.

See also  Power bi date format (Various examples)

Apply Conditional formatting to the Power BI Area chart Line marker [Using Field value]

Here, we will see how to apply conditional formatting based on a field value in the Power BI Area chart marker.

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

conditional formatting in area chart

We will 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[COGS]) <= 15000000, "#a8337d",
SUM('financials'[COGS]) <= 45000000, "#e669b9",
"#de6a73")

This measure in Power BI states that:

  • If the COGS value is less than 15000000, then the column color will be Light Pink (“#a8337d”).
  • Otherwise, if the sales value is less than 45000000, then the column color Dark Pink(#e669b9).
  • Otherwise Orange (“#de6a73”).
apply conditional formatting in Power BI area chart

Once we use the measure to apply conditional formatting to the Column chart, then we will convert the column chart to a Power BI Area chart

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

  • In Power BI Desktop, select the Stacked column chart from the Visualization pane.
  • Drag and drop the COGS and Product columns to the Y-axis and X-axis, respectively.
apply conditional formatting in area chart
  • Then click on the Format pane icon and then expand the Column section.
  • Click on the fx in the color section.
Power BI area chart conditional formatting 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.
How to apply conditional formatting in Power BI area chart

Now, you can see that conditional formatting is applied to the columns of the Power BI Stacked column chart.

How to apply conditional formatting  Power BI area chart
  • To convert the column chart to an Area chart, select the Area chart from the visualization pane.
  • You can see the Area chart marker color changed based on color formatting.
How to apply conditional formatting  in Power BI area chart

This is how we can apply conditional formatting based on the field value in the Power BI Area chart.

See also  How To Use Power Apps Image Control [Detailed Guide]

Some more Power BI articles you may also like:

Conclusion

In this Power BI tutorial, we learned how to apply conditional formatting to the marker in an Area chart.

We explored three examples of implementing conditional formatting in Power BI Area charts: using Gradient, using Rule, and using Field value.

>