Add Reference Bands in Tableau

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

Please visit the 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 Tableau Reference Bands to the Bar Chart. To do this, first, Drag and drop the Color, English Product Name from the Dimension Region to the 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 Tableau reference bands. So, please select the Band Tab.

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

  1. Reference Distributions
  2. Box Plot
  3. Forecasting
  4. Trend Lines
  5. Add Lines using the Parameter
  6. Show or Hide Lines using the Parameter

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 a 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 a Reference Band to the individual cell.
Add Reference Bands 1

Add Tableau Reference Bands to the Entire Table

To add Reference bands to the Entire table, select the Entire Table option in the Scope region. From the screenshot below, 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. In this Reference Bands example, we are selecting the Value as the label.
  • Computation: If you select this option, then the desktop 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

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

  • Total: This option will start or end the Tableau reference Band at the aggregate of all the values of a selected Measure (here, it is the 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 the 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 will perform on individual Pane.
  • And, if we choose per cell, aggregations will perform on individual Cells.

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 Tableau 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 the Band From Line and the 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. For more charts >> Click Here.

Removing Reference Bands in Tableau

To remove the Reference Bands, right-click on the Sales Amount Axis, which will open the context menu. We may have multiple options to remove 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.
  • To remove multiple Reference Bands at one go, select the Remove Reference Lines option.
Remove Reference Bands

Adding Tableau 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 the 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.

Add Reference Bands Per 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 in Tableau. The below screenshot will show you the Reference bands for the Individual Pane.

Add Reference Bands Per Cell

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