KNOWLEDGE BASE

Creating a Calculation that Ignores Filters


Published: 14 Jul 2017
Last Modified Date: 06 Jun 2018

Question

How to create a calculation that remains unfiltered when the rest of the view is filtered.

Environment

Tableau Desktop 9 and later versions

Answer

Option 1: Use the level of detail (LOD) expression FIXED. 

The attached example workbook uses the sample data set Superstore to demonstrate the following directions:

  1. Drag [Region] onto the Rows shelf.
  2. Drag [Sales] onto Text on the marks card.
  3. Drag [Category] onto the Filters shelf.
  4. In the Edit Filter dialog, check all categories and click OK.
  5. Right-click [Category] on the Filters shelf and select Show Filter.
  6. Select Analysis > Create Calculated Field.
  7. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Unfiltered Sales"
    2. In the formula field, create a calculation similar to the following:
      { FIXED [Region] : SUM( [Sales] ) }
  8. Double-click [Unfiltered Sales] in the Data pane to add the field to the crosstab.
Note: As Category filter values are changed, the value of [Unfiltered Sales] will not change.
 

Option 2: Use a parameter to filter every other field in the view

  1. Drag [Region] onto the Rows shelf.
  2. Drag [Sales] onto Text on the Marks card.
  3. In the Data pane, right-click the [Category] field and select Create > Parameter…
  4. In the Create Parameter dialog box do the following and click OK.
    •     In the Name text box, type a name. In this example I will call the parameter "Category Parameter".
    •     Add the value "All" to list.

  • Right-click [Category Parameter] in the Data pane and select Show Parameter Control.
  • Select Analysis > Create Calculated Field.
  • In the Calculated Field dialog box that opens, do the following, and then click OK:
    • Name the calculated field. In this example, the calculated field is named "Sales Filtered by Category".
    • In the formula field, create a calculation similar to the following:

      IF [Category] = [Category Parameter]
      OR [Category Parameter] = "All"
      THEN [Sales]
      END
  • Double-click [Sales Filtered by Category] in the Data pane to add the field to the crosstab.

Additional Information

The FIXED expression will ignore all dimensions except for dimensions included in the dimension declaration. Therefore, the solution should include all dimensions in the view and not the filter dimensions to be ignored. In this example, [Region] is the only dimension in the view. If more dimensions were added to the view, then those dimensions should also be added to the FIXED expression.

Note, Option 1 may not work if additional dimensions are added to the filters shelf. For example, if the calculation was changed to { FIXED [Region], [State] : SUM([Sales]) }, and the state "Washington" only has sales in the "Furniture" category, then when "Furniture" is filtered out of the view, "Washington" will also be filtered out of the view. This means the "West" region will have lower sales.

This happens because FIXED expressions do not explicitly ignore filters. Instead what happens is that the FIXED expression returns the same value over many records in the underlying database. If some of these records containing the repeated value are filtered out, then the FIXED expression will still return the unfiltered value.

 
Did this article resolve the issue?