I read a great blog post today from Andy Kriebel where he shows how to create a 45-degree reference line on a scatter plot:
His solution is very elegant, using a trend line against a secondary axis to create the reference line, however one limitation he points out in his video is that if the X and Y axes have different scales then it doesn’t work.
You either need to live with the visual confusion or fix the axes, however the latter option isn’t a good solution where you have a filter that will cause the range of the data to change significantly. A better solution is to ensure that both axes always show the same range and one way to do this is via reference lines.
First, we need to determine the maximum value on either of the two axes – we can do this with the MAX() function as follows:
WINDOW_MAX(SUM([Selected Year Sales])),
WINDOW_MAX(SUM([Prev Year Sales]))
We can then use this to plot a reference line on both axes – as you can see this forces them to have the same range:
By formatting the reference lines to have no line and no label they are effectively invisible, but they still control the maximum range of the axes:
You can download the workbook with an example of this technique here:
Fantastic tip!! I always forget that reference lines can be used this way.
I need a line like this but rotated 90 degrees. In other words, I have two axis scales from 0 – 100% and I want the line to connect the two 100% points and not run through the 0,0 point of the axis. Any ideas?
Something like this?