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

Add Reference Bands in Tableau 0

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.

Add Reference Bands in Tableau 24

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 to Tableau. 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 in Tableau 1

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 screenshot below, you can observe the available options inside the Entire table Reference Bands option

Add Reference Bands in Tableau 2

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

Add Reference Bands in Tableau 3

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 Tableau Reference Bands example, we are selecting the Value as the label.
  • 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

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 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 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).

Add Reference Bands in Tableau 6

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

Add Reference Bands in Tableau 7

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.

Add Reference Bands in Tableau 8

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.
  • To remove multiple Reference Bands at one go, select the Remove Reference Lines option.
Add Reference Bands in Tableau 9

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.

Add Reference Bands in Tableau 10

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 in Tableau 11

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.

Add Reference Bands in Tableau 13

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

Add Reference Bands in Tableau 14

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