How to add Reference Bands in Tableau

In this article, we will show you how to add Reference Bands in Tableau with an example. For this Tableau add Reference Bands, 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 configurations. The custom SQL query we are going to use for this 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 Bands in Tableau

In this example, we will add Reference Bands 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 the Measures Region to the Rows Shelf, as shown below. By default, Tableau generates the Bar Chart.

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

Once you select the Add Reference Line option, a new window called Add Reference Line, Band, or Box will be opened to configure the reference bands. This article is about adding Reference Bands in Tableau; Please select the Band Tab.

TIP: Please refer to Add Reference Lines in the Tableau article to add the Reference Lines.

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 aggregations (both in Band From and Band To) for the whole table and add Reference Bands to the entire table
Per Pane: This option calculates aggregation (both in Band From and Band To) for individual panes inside the table and adds Reference Band to the individual pane
Per Cell: It will calculate aggregation (both in Band From and Band To) for individual cells inside the table and add Reference Band to the individual cell

Add Reference Bands to the Entire Table

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

Within the Band From and Band To regions, the Value option will decide the Data used by the reference Band. Please select the Measure value we will use for creating a reference band (Here, we are selecting the Sales Amount). Tableau will also allow you to use existing Parameter or New parameters as values

The label option inside the Band From and Band To regions will enable 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 band
Value: It shows the aggregated value of a measure chosen as a label for the Reference band
Computation: If you select this option, then Tableau will display the aggregate Function Name or Measure name as a label for the Reference band
Custom: This option displays the Custom value you specified in the text box as a label for the Reference band

In this Tableau Reference Bands example, we are selecting the Value as the label

The following are the aggregations available inside the Band From and Band To regions:

Total: This option will Start or End the reference Band at the aggregate of all the values of a selected Measure (here it is, Sales Amount)
Sum: This option will Start or End the reference Band at the Sum of the selected Measure.
Constant: This option will Start or End the reference Band at the Specified Constant value
Minimum: This option will Start or End the reference Band at the Minimum of the selected Measure.
Maximum: This option will Start or End the reference Band at the Maximum of the selected Measure.
Average: This option will Start or End the reference Band at the Average of the selected Measure (here it is, Sales Amount)
Median: This option will Start or End the reference Band at the Median of the selected Measure (here it is, Sales Amount)

Remember, as mentioned above, all the aggregations will depend upon Scope.

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

Here we selected the Average aggregated function for the Band From and the Maximum aggregated function for the Band To be field.

The formatting region allows us to format the Reference band Type, Color, Width, and Background color of the Band region. The below screenshot shows how we can format the reference lines (Band From Line and Band To Line).

From the screenshot below, we added a Light Yellow color to fill the space between Band From Line and Band To Line.

Click OK to finish adding reference bands to the bullet Graph. The below screenshot will show you the Reference Band for the Entire Table.

Removing Reference Bands in Tableau

To remove Reference Bands in Tableau, Right Click on the Sales Amount Axis, which will open the context menu. We may have multiple options to remove Tableau reference bands. For instance,

Select the Remove Reference Line option if the report has one Reference Band.
If there is more than one Reference Band, choose the Remove Reference Line option and select the required reference line.
And to remove multiple Reference Bands at one go, select the Remove Reference Lines option.

Adding Reference Bands Per Pane

To add Reference bands Per Pane, select the Per Pane option in the Scope region. From the below screenshot, you can observe that we changed the aggregate function of Band From area to Minimum and the aggregate function of Band To to Average, and we kept the Formatting region unchanged.

Click OK to finish adding the reference band Per Pane to the bullet Graph. The below screenshot will show you the Reference bands for the Individual Pane.

From the above screenshot, Tableau is finding the minimum value for individual panes and calculating the Average for individual panes rather than the entire table.

Adding Reference Bands Per Cell

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

From the below screenshot, we changed the Band From value from Sum of Sales Amount to the Constant value of 622,007.1 and the aggregate function of Band To to Maximum.

Click OK to finish adding reference bands Per Cell. The below screenshot will show you the Reference bands for the Individual Pane.

From the above screenshot, you can notice that Tableau finds the maximum value for individual Cells rather than the entire table.