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. 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 are going to 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 Measures Region to 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 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 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 add Reference Band to the individual pane
• Per Cell: It will calculate aggregation (both in Band From and Band To) for individual cell 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, 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 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 are going to use for creating a reference band (Here we are selecting Sales Amount). Tableau will also allow you to use existing Parameter or New parameters as values

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 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 reference Band at the Sum of selected Measure (here it is, Sales Amount)
• Constant: This option will Start or End reference Band at the Specified Constant value
• Minimum: This option will Start or End reference Band at the Minimum of selected Measure (here it is, Sales Amount)
• Maximum: This option will Start or End reference Band at the Maximum of selected Measure (here it is, Sales Amount)
• Average: This option will Start or End reference Band at the Average of selected Measure (here it is, Sales Amount)
• Median: This option will Start or End reference Band at the Median of selected Measure (here it is, Sales Amount)

Remember, All the aggregations, as mentioned above, 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 Average aggregated function for the Band From and Maximum aggregated function for the Band To filed

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

From the below screenshot, you can observe that We added Light Yellow color to fill the space between Band From Line and Band To Line

Click OK to finish adding reference bands to bullet Graph. 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 will open the context menu. We may have multiple options to remove Tableau reference bands. For instance,

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

Adding Reference Bands Per Pane

To add Reference bands Per Pane, Please 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 aggregate function of Band To to Average, and we kept the Formatting region unchanged

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

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

Adding Reference Bands Per Cell

To add Tableau Reference bands 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 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 aggregate function of Band To to Maximum

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

From the above screenshot, you can notice that Tableau is finding the maximum value for individual Cell rather than the entire table