Add Reference Bands in Tableau

In this article we will show you, How to add Reference Bands 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 Bands in Tableau 9.1

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 will generate the Bar Chart for you

In order 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 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 bands. This article is about adding Reference Bands so, Please select the Band Tab.

TIP: Please refer 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 entire table and add Reference Bands to the entire table
• Per Pane: If you select this option then, Tableau will calculate aggregation (both in Band From and band To) for individual panes inside the table and add Reference Band to the individual pane
• Per Cell: If you select this option then, Tableau 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

In order to add Reference bands 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 Bands option

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

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

In this example, we are selecting the Value as label

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

Here we selected Average aggregated function for the Band From and Maximum aggregated function for the Band To filed

Formatting region allows us to format the Reference band Type, Color, Width and Background color of Band region. Below screenshot will show you, 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

In order 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 reference Lines. 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.
• And, If you want to remove multiple Reference Line at one go then, you can simply select Remove All Reference Lines option.

Adding Reference Bands Per Pane

In order to add Reference bands Per Pane, Please select the Per Pane option in Scope region. From the below screenshot you can observe that, we changed the aggregate function of Band From region 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 you can notice that, Tableau is finding minimum value for individual panes and calculating Average for individual panes rather than entire table

Adding Reference Bands Per Cell

In order to add Reference bands 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 Formatting

From the below screenshot you can observe that, 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 maximum value for individual Cell rather than entire table

Thank You for Visiting Our Blog