View the original community article here

Last tested: Sep 20, 2019
 

The Problem

Some users want reference lines in their visualization that use metrics that are not in our standard drop-down options. They could always use a custom value, but that value is static, so if it is based on a calculation of the data, it doesn't quite suit their needs. This help center article explains how to create dynamic reference lines via table calculations, but if value labels are included, this can look a little messy. This post is how to address the "value label" problem.

Note:

This is an imperfect solution being that I have not yet figured out how to hide the null value on bar/column visualizations. This works best with charts where the reference value will be off the main visualization. Then you can use a dark color for the data and the reference value to mask the null value.
Less ideal use case:


Better use case:

The Workaround

After creating the table calculation for the dynamic reference line:

1. Create a second table calculation that returns the value of your reference line table calculation on a single row. (will be referred to as "Label Calculation")
   Ex:
   if(row() = count(${dimension}), ${reference_line_calculation}, null) for the last row
   if(row() = 1, ${reference_line_calculation}, null) for the first row
2. Change the order of the table calculations so that the single returned value comes before the reference line calculation.
   Order in: Measure, Label Calculation, reference_line_calculation
   
3. In the Edit modal of the visualization, under the Series tab, customize the Label Calculation to type line and change its color to transparent in the Custom color option.
   
4. In the Edit modal, under the Values tab
   1. If not already on, turn on Value Labels
   2. Customize the value colors for each of the series ending with transparent to hide the values from the reference line itself
      Ex: purple, black, transparent
      1. Purple to match the vis color
      2. Black to make the trend line value stand out
      3. Transparent to hide the line labels