Adding an average reference line in seasonality charts

A time series focussing on seasonality
A time series focussing on seasonality. Click here to see and interact with the full dashboard.

See the thick line in the chart above? That shows the average number of fatalities in a month for all years in the dataset.

It’s the equivalent of drawing an average reference line for each cell in the view, which you can see below:

Using Average Reference Lines
Using Average Reference Lines

The reference lines are quick and work, but they’re not attractive and are a little difficult to interpret.

To draw the line you need some table calculations.

I’ve recreated the chart using the sample Superstore data and you can see the workbook by clicking on the image below:

Click the image to view the workbook
Click the image to view the workbook

Making the average line

To make this chart, I start with a basic seasonality view: [Month] on Columns, [Year] on Colour/Detail shelf.

I then need to work out the window average in each month. here’s the calculation:

window average

Drop that onto the Row shelf and set the Compute Using settings as follows:
compute using for window average

Now you should have a chart with the regular measure and the average calculation on the Row shelf. Right click one of the measures and choose Dual Axis. Then right-click on one of the axis and synchronise them.

That’s the basic chart done. I had to tweak the Colour, Transparency and Size a little to get a result I was happy with.

An anti-aliasing problem

This is pretty much complete.  But there’s one thing that’s niggling. The average line is jagged:

I can't help but hate anti-aliasing
Argh! Jagged edges!

What’s going on here? Well our average line is actually multiple lines, one for each year in the dataset, drawn on top of each other.

How do we solve that? I created a new calculation:

FIRST()==0

If you set this with the same Compute Using… setings as the calculation above, you get a True/False value. The True value is applied to just one of the lines being drawn on the chart.

Drop this field on the size shelf, right-click on the False mark in the legend, and choose Hide.

Hide false

This solves the anti-aliasing problem and makes the view that small but significant bit nicer to look at.

Conclusion

As you can see in the image below, what I’ve achieved is the equivalent of doing a per cell reference line, but made it much more attractive:

My solution on the left. Default reference lines on the right
My solution on the left. Default reference lines on the right

What do you think? Is this the best way of showing seasonality? Is there a more efficient way of doing this using simpler calculations?

Click the image below to see and play with the complete workbook:

My entry - click to see it bigger
My entry – click to see it bigger

5 thoughts on “Adding an average reference line in seasonality charts”

  1. “I can’t help but hate anti-aliasing” You mean you hate aliasing, right? Anti-aliasing is what fixes the jaggies. Then again, maybe you meant anti-aliasing implementation .. and–if so–then fair enough.

    It looks great!

Leave a Reply

Your email address will not be published. Required fields are marked *