How to add Reference Lines in Tableau 9.1

In this article we will show you, How to add Reference Lines in Tableau with 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. Custom SQL query we are going to use for this report is:

SQL Query

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 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 as shown below. By default, tableau will generate the Bar Chart for you

In order 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 opened to configure the reference lines. This article is about adding Reference Lines so, Please select the Line Tab and we will show you remaining options in the coming 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 entire table and add Reference Line to the entire table
Per Pane: If you select this option then, Tableau will calculate aggregation for individual panes inside the table and add Reference Line to the individual pane
Per Cell: If you select this option then, Tableau will calculate aggregation for individual cell inside the table and add Reference Line to the individual cell

Add Reference Lines to the Entire Table

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

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

Following are the aggregations available in Reference Line box:

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

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

If we select Entire Table, aggregations will perform on entire table
If we select Per pane, aggregations will perform on individual Pane
And if we select Per Cell, aggregations will perform on individual Cell

Label allows you 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 selected measure as label for the Reference Line
Computation: If you select this option then Tableau will display the aggregate Function Name or Measure name as 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 label for the Reference Line

In this example, we are selecting the Value as 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

Formatting region allows us to format the Reference Line Type, Color, Width and Background color of above region and below region. Below screenshot will show you, how we can format the reference lines.

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

From the below screenshot you can observe that, We added Light yellow color to fill the space below the reference Line

Click OK to finish adding 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 ca observe the Line and Confidence interval option is displaying the confidence interval of 95

Removing Reference Lines in Tableau

In order 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 shown below. For instance,

If your tableau report holds only one Reference Line then you can select Remove Reference Line option.
If your tableau report holds more than one Reference Line then you have to select Remove Reference Line option and then select the required reference line.
Or, If you want to remove multiple Reference Line at one go then, you can simply select Remove All Reference Lines option.

Adding Reference Lines Per Pane

In order to add Reference Lines Per Pane, Please select the Per Pane option in 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 entire table

Adding Reference Lines Per Cell

In order to add Reference Lines Per Cell, Please select the Per cell option in 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 entire table or pane

Editing Reference Lines in Tableau

In order to Edit the existing Reference Lines, Right Click on the Sales Amount Axis will open the context menu. Please select the Edit Reference Line option from the context menu as shown below.

Thank You for Visiting Our Blog