Creating a Calculation that Ignores Filters

Published: 14 Jul 2017
Last Modified Date: 28 Mar 2018


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


Tableau Desktop 9 and later versions


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?