How to add Reference Lines in Tableau

In this article, we will show you how to add Reference Lines in Tableau with an example. For this, we are going to write the Custom SQL Query against the SQL Server Data Source. So, Please visit Connecting Tableau to SQL Server article to understand the configurations.

The custom SQL query we are going to use for this Reference Lines in Tableau report is:

SELECT ProdSubCat.EnglishProductSubcategoryName, 
	   Tery.[SalesTerritoryCountry], 
	   Prod.EnglishProductName, 
	   Prod.Color, 
	   Fact.OrderQuantity, 
	   Fact.TotalProductCost, 
	   Fact.SalesAmount, 
	   Fact.TaxAmt, 
          Fact.[Freight],
         Fact.OrderDate
FROM DimProductSubcategory AS ProdSubCat
   INNER JOIN
     DimProduct AS Prod ON ProdSubCat.ProductSubcategoryKey = Prod.ProductSubcategoryKey 
    INNER JOIN
     FactInternetSales AS Fact ON Prod.ProductKey = Fact.ProductKey 
   INNER JOIN
     [DimSalesTerritory] AS Tery ON Tery.[SalesTerritoryKey] = Fact.[SalesTerritoryKey]

Add Reference Lines in Tableau

In this example, We are going to add Tableau Reference Lines to the Bar Chart. To do this first, Drag and Drop the Color, English Product Name from Dimension Region to Columns Shelf, and Sales Amount from Measures Region to Rows Shelf. By default, Tableau will generate the Bar Chart.

To add Reference Lines in Tableau, Right Click on the Sales Amount Axis will open the context menu. Please select the Add Reference Line option from the context menu

Once you select the Add Reference Line option, a new window called Add Reference Line, Band, or Box will open to configure the reference lines. This article is about adding Reference Lines. So, Please select the Line Tab, and we will show you the remaining options in the next articles.

From the below screenshot you can observe, we have three options for the Scope region:

Entire Table: If you select this option then, Tableau will calculate aggregation for the whole table and add Reference Line for the entire table
Per Pane: It will calculate aggregation for individual panes inside the table, and add Reference Line to the individual pane
Per Cell: Select this option to calculate aggregation for an individual cell inside the table, and add Reference Line to the individual cell

Add Reference Lines to the Entire Table in Tableau

To add Tableau Reference Lines to the Entire table, Please select the Entire Table option in the Scope region. From the below screenshot you can observe, the available options inside the Entire table Reference Lines option

Within the Line region, the Value option will decide the Data used by the reference Line. Please select the Measure value we are going to use for creating a reference line (Here we are selecting Sales Amount). Tableau will also allow you to use existing or New parameters as values

The following are the aggregations available in Reference Line box:

Total: This option will place a line at the aggregate of all the values of a selected Measure (here it is, Sales Amount)
Sum: This option will place a line at the Sum of selected Measure (here it is, Sales Amount)
Constant: It puts a line at the Specified Constant value
Minimum: It places a line at the Minimum of selected Measure (here it is, Sales Amount)
Maximum: This option puts a line at the Maximum of selected Measure (here it is, Sales Amount)
Average: This option places the line at the Average of selected Measure (here it is, Sales Amount)
Median: Place a line at the Median of selected Measure (here it is, Sales Amount)

Remember, All the aggregations mentioned above will depend upon Scope.

If we select the Entire Table, aggregations will perform an entire table
If we choose Per pane, aggregations will show on individual Pane
And if we choose per cell, aggregations perform on individual Cell

The label allows you to configure whether you want to see the Label or not. This option has different fields:

None: If you select this option then Tableau will not display any label for the Reference Line
Value: If you select this option then Tableau will display the aggregated value of a measure chosen as a label for the Reference Line
Computation: If you select this option then Tableau will display the aggregate Function Name or Measure name as a label for the Reference Line
Custom: If you select this option then Tableau will display the Custom value you specified in the text box as a label for the Reference Line

In this example, we are selecting the Value as the label

Please specify whether you want to display the Reference Line, or Reference Line and Confidence interval, or Confidence interval Only. For now, we are selecting Line Only option

The formatting region allows us to format the Reference Line Type, Color, Width, and Background color of the above area and below area. The below screenshot shows how we can format the reference lines.

From the below screenshot, you can observe, we added Light pink color to fill the space above the reference Line

From the below screenshot you can see that We added Light yellow color to fill the area below the reference Line

Click OK to finish adding a reference line to bullet Graph. Below screenshot will show you the Reference Line for the Entire Table

Let us change the Line options from Line Only to Line and Confidence interval as shown below

From the above screenshot, you can observe the Line and Confidence interval option is displaying the confidence interval of 95

Remove Reference Lines in Tableau

To remove Reference Lines in Tableau, Right Click on the Sales Amount Axis will open the context menu. We have multiple options to remove reference Lines, as we have shown below. For instance,

If your tableau report holds only one Reference Line, select Remove Reference Line option.
If the report holds more than one Reference Line, select the Remove Reference Line option, and then select the required reference line.
To remove multiple Reference Line at one go, select Remove All Reference Lines option.

Add Reference Lines in Tableau Per Pane

To add Tableau Reference Lines Per Pane, Please select the Per Pane option in the Scope region. From the below screenshot you can observe that we haven’t changed anything with Line or Formatting

Click OK to finish adding reference line Per Pane to bullet Graph. Below screenshot will show you the Reference Line for the Individual Pane

From the above screenshot, you can notice that Tableau is calculating Average for individual panes rather than an entire table

Add Tableau Reference Lines Per Cell

To add Tableau Reference Lines Per Cell, Please select the Per cell option in the Scope region. From the below screenshot you can observe that we haven’t changed anything with Line or Formatting

Click OK to finish adding reference line Per Cell. Below screenshot will show you the Reference Line for the Individual Cell

From the above screenshot, you can notice that Tableau is calculating Average for individual Cell rather than an entire table or pane

Edit Reference Lines in Tableau

To Edit the existing Tableau Reference Lines, Right Click on the Sales Amount Axis opens the context menu. Please select the Edit Reference Line option from the context menu.