Our Certified Tableau Trainers are asked a myriad of questions each day. In this series, they share some answers.

Every single day, I get emails from former students asking for help with everything from visual best practices to calculated fields. Sometimes the questions are quick while others require more thought. Some may be deceptively simple questions that turn into something more complicated than first thought.

What started as a relatively straightforward question from a student led me into a quest to better understand how aggregations work in conjunction with each other within reference lines.

Here is the question that kicked all this off:

Goran, from a virtual Desktop II class, was trying to compare this year’s monthly sales against last year’s maximum sales.

Note: For the purposes of demonstration, I am using the Sample-Superstore data set that comes preinstalled with Tableau Desktop.

The first calculation I will make will isolate 2018 sales:

IF YEAR([Order Date])=2018 THEN [Sales] END

Then I will make a very similar field to isolate 2017 sales to use in a reference line:

IF YEAR([Order Date])=2017 THEN [Sales] END

When I drag over the 2017 Sales field onto Detail to use it in my reference line, I can change the aggregation to Maximum. Then, when I actually create my reference line, I can change the aggregation of the line to sum.

Let’s take a look at this first permutation of our Maximum Sales on Detail, with our reference line set to the Sum of our Maximum Sales:

Our reference line appears at $79,167. If we look at a breakdown of the data, we see that this is the sum of the single highest transactions for each month of 2017.

Is this what we are looking for? To really answer this question, we must ask ourselves: What is the reference line that we are really trying to see? A line showing us the total of all those single transactions, or a line at the value of 2017’s single highest month of sales? If the latter is the case, we need to switch around our aggregations.

If we change our MAX(Sales) pill to SUM(Sales) and change our reference line to Maximum, we will now get a line at the value for the single highest month in 2017, December:

You can try out many different combinations of aggregations on both your pill and your reference line, but you need to be cognizant of what the business problem is. In this case, since we wanted to compare each month of 2018 to 2017’s highest monthly sales, this combination works.

Make sure whenever you are creating a reference line that the aggregation is correct – and don’t skip the step of checking your work in a crosstab if you want to be sure!

Thank you for the question, Goran, and for the challenge!