Quick Tips: Line Chart and Area Chart Conditional Formatting in Power BI

Line Chart and Area Chart Conditional Formatting in Power BI

In this post I show you a very quick trick to format Line Chart and Area Chart conditionally in Power BI. As this is a “Quick Tip” I’m going to keep this post really short.

One of my customers asked me to show time series in line charts and area charts. But she want’s it to be conditionally formatted based on the average value over time. Let’s keep it simple, she wants to show “Sales by Year Month” in line chart, but, highlight the data points that are below “Average Sales per Year Month”. As you may know, we currently do not have the luxury of formatting line charts and area charts. But wait, this post is all about that. Let’s dig into it.

From the above scenario, you perhaps already guessed that we need to create a measure which defines the colour based on “Average Sales per Year Month” to be able to format the chart conditionally. If any data point is below the “Average Sales per Year Month” then we highlight it in Orange, if it is above the “Average Sales per Year Month” then we stick to the default colour.

Let’s do it.

It’s really easy to do, just follow the steps below:

  • Put a column chart on the canvas
  • Add the “Year Month” column to the column chart’s Axis
  • Add the measure to the column chart’s Values
  • Format the column chart conditionally, more on this later
  • Turn the column chart to line chart or area chart by selecting line/area chart from Visuals pane

That’s it. Let’s look into more details of the solution.

This method perfectly supports conditional formatting, but you have to pay attention to the field you’re using on X-Axis. You need to have continuous values on X axis, therefore the values must be either a “number” or “date” (datetime). What you usually see in the values of the “Year Month” column is a string like “2019 Jan”, “2019-Jan”, or “2019 – Jan”, right? So you just need to convert “2019 Jan” or “2019-Jan” to “date” data type either in Power Query or via the “Column Tools” from the ribbon. If we don’t convert string to date and put the “Year Month” on X Axis, we have “Categorical” type on X Axis and that’s NOT what we want. Another point that’s worthwhile to mention is that this method only works when we have one measure on the chart. Unfortunately this method wouldn’t work with multiple measures.

Have you used this method? Do you know a better method to do the same thing? Please share your thoughts in the comments section below.

7 thoughts on “Quick Tips: Line Chart and Area Chart Conditional Formatting in Power BI

  1. Hello,
    I have a question related to this,

    Is there any way to use this method and have these conditional formatting, and in the same time have the x-axis showing all the months names?

    1. Hi Muhammad,

      Welcome to BIInsight.
      As stated at the very last paragraph this method only works for “CONTINUOUS” values on X axis which either a number or date not for “CATEGIRICAL” values like string.
      While Month name is string you cannot use this method to force conditional formatting in Line/Area charts.
      Hopefully that helps.

      Cheers.

  2. I am having an issue while conditional formatting. I have implemented conditional formation but i want it for only one single line in line graph. the Formatting is being implemented in other line/value too.

    1. Hi Dhiraj,

      Thanks for your comment. This method only works for one measure, meaning one line on the chart only.
      Unfortunately, this is not going to work for multiple measures.
      I have updated the blog to reflect this limitation.

      Cheers.

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.