KNOWLEDGE BASE

Implementing a Top N Filter While Maintaining Correct Percentages


Published: 16 Apr 2013
Last Modified Date: 13 May 2016

Issue

How to filter the top N of a dimension based on a measure while maintaining the percent of total over the whole data set.

Environment

Tableau Desktop

Resolution

Option 1: Use INDEX()

The following instructions can be reviewed in top_N_correct_percentage.twbx attached below.

Instead of implementing a top N filter on the dimension, do the following:
  1. Create a calculated field with the formula INDEX().
  2. Sort the dimension based on the relevant percentage or the measure on which the percentage is based.
  3. Place the INDEX() measure on the Level of Detail shelf.
  4. Filter on INDEX() by placing it on the filter shelf and implementing an At Least slider.

Option 2: Use LOD calculation

The following instructions can be reviewed in Top N LOD % of Total.twbx attached below.
  1. Create a new percent of total calculation using a FIXED LOD expression to find the total for the whole data set. The final calculation might be similar to the following:
    SUM( [Sales] ) /
    SUM( { FIXED : SUM([Sales]) } )

Cause

As soon as a filter is implemented, the filtering operation limits the rows that can contribute to the total operation, thus skewing the percentage figures.
Did this article resolve the issue?