Add Reference Lines in Tableau

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

Please visit Connecting Tableau to SQL Server article to understand the Tableau 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.

Add Reference Lines in Tableau 1

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

Add Reference Lines in Tableau 2

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 in Tableau 0

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

Add Reference Lines in Tableau 3

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

Add Reference Lines in Tableau 4

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
Add Reference Lines in Tableau 5

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

Add Reference Lines in Tableau 6

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

Add Reference Lines in Tableau 7

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 Bar Chart shows how we can format the reference lines.

Add Reference Lines in Tableau 8

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

Add Reference Lines in Tableau 9

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

Add Reference Lines in Tableau 10

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

Add Reference Lines in Tableau 11

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

Add Reference Lines in Tableau 12

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 13

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

Add Reference Lines in Tableau 14

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

Add Reference Lines in Tableau 15

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

Add Reference Lines in Tableau 16

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

Add Reference Lines in Tableau 17

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.

Add Reference Lines in Tableau 18