Two quick ideas on reference lines

Here are two quick questions that were raised recently, both regarding the use of reference lines.

Question: How to create a custom reference line?

In this case, the user wanted to show a median as a reference line but only for values that exceeded a certain threshold.

So, in effect he had a bar chart and a reference line but this ref line showed the overall median for all bars. But the user wanted to only show the median for values (bars) exceeding 100,000.

The solution to this is pretty simply. Create a calculation

//Conditional median
WINDOW_MEDIAN(if sum([Sales]) > 100000 then SUM([Sales]) else null end)

Put it on the details and add it as a reference line. Result:

Question: When using a forecast, make the reference line only reference the actual values w/o the forecast

So, in this case what happens if you use the Tableau forecasting feature and add a reference line, the reference line, in this case the average, will also include values from the just created forecast.

How do we get around this?

Simply right click the measure you want to use for forecasting, here “Sales”. Duplicate it. Put the copy on your details. Make sure to remove the forecast on the details (the little upward arrow implies it is considering the forecast values):

To remove the forecast, right click, go to forecast result and set to “none”:

We can now use this copy as a new reference line and it will not consider the forecast values.

As you can see, when we ignore the forecast, the reference line is below the general reference line and – if you want to check – matches the reference line if we just add an average ref line to this data w/o adding a forecast.

And that’s my two quick tips for today.

I hope you find that useful and as always, appreciate your feedback.

Until next time.

Steffen