1. calculate formula for the reference line 2. use background colour of 1st dimension to define colour and insert formula (if everything fails define fixed axis). 3. for the reference line, I used just an overlay line on the bottom 😉
as you seem to need a linear trendline and not just a diagonal line there is the problem that you have to display a line function inside a scatter chart.
To be honest, I have no clue how this should work, even with the reference line.
So the way out is a second line chart with the same axis, invisible apart from the line itself in the background.
1. calculate formula for the reference line via linest...() 2. use background color of 1st dimension to define color and insert formula 3. Fix axis to max +10% 4. for the reference line, create separate line chart with same fixed axis 5. put underneath and synchronize using "show grid". 6. when all fits, make all colors of the line chart transparent except for the trend line
-> note that the colors and trendline will react on selections, too
Alternatively to avoid all this mess with the synchronized charts, you could as well use a line chart with just symbols instead. The problem with potentially not unique y-values can be solved by adding a really small random value to the x-values while loading.
PatientDays + (Rand()/100000) asPatientDays , GLAmount, Date
Then you can simply add a linear trendline for your first expression only(GLAmount) where you wont even need the linest()
or create any other calculated reference line as a second expression.
If you want the date to be shown too, just add another expression only(Date) and tick "text as popup".
Downside of this dirty little trick: I have no idea how to erase the delta for display. In my version no rounding or number formatting helps.