Dynamic Reference Lines

by Seffana Mohamed-Ajaz

There are certain times where I would like to see a chart in a general overview and then drill down in the same space. I am going to walk you through how to make a dynamic reference line, have reference only appear on a certain condition.

In this exercise we’ll be using the Sample-Superstore dataset and see the Sales performance of Sub-Categories in the Central region. I would like to see which Sub-Categories have met and exceeded a monthly target of $1,000. I would like a reference line to only appear when I filter to one month in view and “disappear” when have multiple (or all) months selected in view.

First, make a sheet that looks at the SUM of Sales for Sub-Category in only the Central region and sort descending. I am only interested in seeing the Sales in 2018, so I’ll be filtering out the other years. See image below.

Image 1: View of sub-category sales in Central region for 2018

Since we are looking into sales targets being met on a monthly level, for each month, we should count the months.

Do this by creating a calculated field as seen in Image 2.

Image 2: Number of months calculation

(If you would like to learn more about Tableau dates in calculations then check out DS7’s Stephanie’s blog about it.)

Drag the Order Date field into the filters pane and select Month / Year, and add to Context as seen in Image 3. Do the same for the Year filter field, too. The fields should turn grey in the filters pane.       

Image 3: Add Year and Month/Year filter fields to context, the fields should then turn grey

We would like to make a reference line that would appear when we choose to investigate sales in one particular month as opposed to multiple months. To do this we should make another calculated field that would help us investigate the targets met for each month, as seen in Image 4.

Image 4: Creating a calculated field that would produce a target of $1,000 for each month

This calculation would tell Tableau that when the number of months in the view is one (i.e., when we filter for one month), it should show our monthly target sales of $1,000.

Drag both the Number of Months calculated field and the Target when one month calculated field into Detail card in the Marks pane. Select one month in the worksheet view, e.g. January 2018. In the Analytics pane, add a Reference Line for a Table.

In the Line section there should be a drop down selection for Value, select AGG(Target when one month) and make it a Total rather than Average and choose None for Label. As demonstrated in the video below, the reference line should only appear when you select one month, and should disappear when multiple months are selected.

Video showing how to make reference line when one month in view

To make the distinction between those that are above target and below target, a calculated field should be made, see Image 5.

Image 5: Create Above Target field to show which sub-categories are above or below target

This calculation would return the average sum of sales for each Region, for each Sub-Category that exceeds the monthly target. If we do not fix it on Region then it will calculate the average sum of sales for each Sub-Category for all the regions in the dataset. Then, drag the Above Target calculated field in the Color card in the Marks pane.

Image 6: Bars in green are sub-category sales that are above target in January 2018 and those who are below target are coloured grey

Now it is easier to see which Sub-Categories are above or below target on a monthly level.


Seffana Mohamed-Ajaz

Fri 01 Mar 2019

Thu 28 Feb 2019

Wed 27 Feb 2019

© 2022 The Information Lab Ltd. All rights reserved.