Add Reference Lines in Tableau 12

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 Query against the SQL Server Data Source.

Please visit Connecting to 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, it will generate the Bar Chart.

Basic Report 1

To add Reference Lines in Tableau, Right Click on the Sales Amount Axis, which 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 option, a new window called Add Reference Line, Band, or Box will open to configure them. 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 that 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 a Reference Line for the entire table
  • Per Pane: It will calculate aggregation for individual panes inside the table and add to the individual pane
  • Per Cell: Select this option to calculate aggregation for an individual cell inside the table, and add to the individual cell
Choose Line and Scope as Per pane 0

Add Reference Lines to the Entire Table in Tableau

To add Reference Lines to the Entire table, Please select the Entire Table option in the Scope region. The screenshot below shows you the available options inside the Entire table option.

Choose Line and Scope as Entire Table 3

Within the Line region, the Value option will decide the Data used by it. Please select the Measure value we are going to use for creating it, and here, we are selecting Sales Amount. It 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 the Tableau Reference Lines 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 the 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 the selected Measure (here it is, Sales Amount)
  • Maximum: This option puts a line at the Maximum of the selected Measure (here it is, Sales Amount)
  • Average: This option places the line at the Average of the selected Measure (here it is, Sales Amount)
  • Median: Place a line at the Median of the selected Measure (here it is, Sales Amount).

Remember, All the aggregations mentioned above Reference Lines 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
Change the Aggregate Function 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, it will display the aggregated value of a measure chosen as a label.
  • Computation: If you select this option, it will display the aggregate Function Name or Measure name as a label.
  • Custom: If you select this option, it will display the Custom value you specified in the text box as a label for this.

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 Tableau Reference Line, Line and Confidence interval, or Confidence interval Only. For now, we are selecting Line Only option

Choose Line Only option 7

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

Format Reference Lines in Tableau 8

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

Fill above the Lines Area 9

From the below screenshot, you can see that We added a Light yellow color to fill the area below it.

Fill below with colors 10

Click OK to finish adding a Tableau reference line to the bullet Graph, and the below screenshot will show you this 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.

Choose Line and Confidence Interval 12

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

Remove Reference Lines in Tableau

To remove Reference Lines, 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 report holds only one, select the Remove Reference Line option.
  • If the report holds more than one, select the Remove Reference Line option and then select the required one.
  • To remove multiple ones at one go, select the Remove All Reference Lines option.
Right-click and choose Remove option 13

Add Reference Lines in Tableau Per Pane

To add 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.

Select Per Pane Option 14

Click OK to finish adding the reference line Per Pane to the bullet Graph. The 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 it is calculating the Average for individual panes rather than an entire table.

Add Tableau Reference Lines Per Cell

To add 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

Choose Per Cell Option 16

Click OK to finish adding the reference line Per Cell. The below screenshot will show this for the Individual Cell.

Add Reference Lines in Tableau 17

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

Edit Reference Lines

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

Add Reference Lines in Tableau 18