Line Graph Conditional Formatting

[Anonymous]

Hi,

I was wondering if there was a way to color code a line graph. I looked it up, and on some versions of Power BI, there is either an advanced option on the Data colors editor, or 3 vertical dots to indicate the advanced option. On my version, there are neither even for the clustered column chart visualization.

I was wondering if there was a formula I could use to say, if total for 2020<2019 then green, else if 2020>2019 and 2020-2019>100 then red, else yellow or something along those lines... 

Please let me know if this question is confusing 

Thank you!
Sarah

[jthomson]

I don't think there's anything like this, but maybe you could try to visualise your data in a different way and make a measure to show the year on year difference - drop that into a line chart, then you can use the constant lines feature to highlight the points where you'd want the formatting to change?

[MFelix]

Hi @jthomson ,

Condittional formatting is not possible on line charts.

What you can do is use measure to calculate the higher and lower and then for those measure place markers with different colours and hide the line.

What I did was to create 5 measures:

Current Year = SUM('Table'[Value])


Previous Year = CALCULATE(SUM('Table'[Value]);DATEADD('Calendar'[Date];-1;YEAR))

Previous 2 year = CALCULATE(SUM('Table'[Value]);DATEADD('Calendar'[Date];-2;YEAR))

Higher = IF([Current Year]<[Previous Year];BLANK();[Current Year])

Lower = IF([Current Year] < [Previous Year];[Current Year];BLANK())

Then place the measures on the line chart and final result will be this one (colors are not the best ones):

I also used a calendar table and a slicer to select the year.

Check PBIX file attach.

[Anonymous]

I feel like I am close, but I am not sure how to encorperate the Red and Green dots if it is above or below last years totals...

[MFelix]

Hi @Anonymous 

You need to go to the line chart options:

• Shapes
• Customized series
• Select the higher and lower
• Line size 0
• Show Marker on
• Select the marker and color to your liking

[Anonymous]

hi @MFelix 

for some reason my version does not have the options after line style like you do...