Conditional Format a Line Chart in Power BI

Have you ever wonder how to conditional format a Line Chart in Power BI which displays colour coded markers?

I came across this amazing tip from Curbal which explains how to do this. This blog is how I used the example by Curbal for my own use.

I was recently tasked with creating a Dashboard Visual in Power BI which showed a monthly percentage trend of timesheets that had been submitted on time.
The aim was to have a visual which :

• Showed the monthly trend (a line chart can do this easily)
• Visually showed if the value on each month had met its target (not possible do with just a line chart)

The Line Chart was the perfect visual to use; however, there was no simple way to apply a KPI colour on each month. The markers section on the Line Chart did not have the option to apply colour coding, see image below

Solution

As with anything i do in Power BI, the first thing i did was present my data in a table format.

Below is the raw (Sample) data i need plotting on my Line Chart, with each month having a marker that was colour coded.

The Submitted Time % was calculated via a Measure Called [TimeSheetCompletion] (For the purposes of this blog, i will not go into how i got the values and assume you have you base values.)

Step 1

Create categories to bucket your values allows you to sort the Submitted Time in a relevant buckets which can be used when applying conditional formatting.

Below is the Switch Statement i wrote to achieve this :

KPI Code =
SWITCH (
    TRUE (),
    [TimeSheetCompletion] >= 0.95, 1,
    [TimeSheetCompletion] <= 0.94
        && [TimeSheetCompletion] >= 0.90, 2,
    [TimeSheetCompletion] <= 0.89
        && [TimeSheetCompletion] > 0, 3
)

This code is a simple Switch Statement which categorises the Submitted Time into buckets of 1, 2 or 3.

• If the Submitted Time is Greater than or equal to 95%, then the output would be 1
• If the Submitted Time was Less than or equal to 94% & greater than or equal to 90%, then the output would be 2
• If the Submitted Time was less than 80% and greater than 0%, then the output would be 3.

Once we apply that KPI Code measure to the table, we get the following table

Step 2

Remove the KPI Code and Convert the Table above to a Stacked bar chart, as below

Step 3

Format Visual, The Bars Section has Colours that allows you to apply conditional formatting.

Step 4

Apply Conditional Formatting
Select the fx button, under Format Style, choose Gradient. Select the KPI – Code Measure created above as the field to base this on.
Add a Middle Colour and set the Custom Values to 1 2 and 3 and choose a colour for each Value, as below

Step 5

Your stacked bar chart should now look like this

Step 6

Now convert the Stacked Bar Chart to a Line Graph.
Voila; we have a colour coded Line Chart.

Hope that helps someone along the way 🙂
If there is a better or more efficient way of achieving the above, please let me know in the comments.

Thanks