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.
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 support 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 normally see in values of “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.
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.