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
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.)
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
Remove the KPI Code and Convert the Table above to a Stacked bar chart, as below
Format Visual, The Bars Section has Colours that allows you to apply conditional formatting.
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
Your stacked bar chart should now look like this
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.