KNOWLEDGE BASE

Creating a Calculation that Ignores Filters


Published: 14 Jul 2017
Last Modified Date: 29 Sep 2017

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

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.

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.
 
Did this article resolve the issue?