KNOWLEDGE BASE

Calculating Percent of Total for Filtered Records


Published: 01 Sep 2016
Last Modified Date: 04 Sep 2017

Question

When using a filter, percent of total does not reflect the percent of filtered records to the total records in the data. 

Environment

Tableau Desktop

Answer

Option 1

In order to calculate the percent of total records, we need to reference an unfiltered dataset. This can be done using a FIXED level of detail calculation, as follows:

[Number of Records] / {FIXED [Level],[of],[Detail],[in],[View]: SUM([Number of Records])

Important Notes:

  • [Level],[of],[Detail],[in],[View] should be replaced by a dimensionality declaration that matches the view level of detail.
  • Using FIXED will bypass filters in the view that are not added to context, so if other filtering should affect the FIXED expression, it will need to be added to context.

Option 2

As an alternative, this can be accomplished by creating a duplicated data source to reference in the equation:

(SUM([Number of Records])/SUM([Duplicate].[Number of Records]))
 However, please note that using a level of detail calculation is much more efficient than duplicating the data source. Using a level of detail calculation (option 1) for this issue is recommended. 
Did this article resolve the issue?