Quick Tips: 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.