Loading

Implementing a Top N Filter While Maintaining Correct Percentages

Date de publication: Apr 13, 2024
Description
How to filter the top N of a dimension based on a measure while maintaining the percent of total over the whole data set.

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.
Résolution

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]) } )
Numéro d’article de la base de connaissances

001473605

Pièces jointes

top_N_correct_percentage.twbx

457 KB

Top N LOD % of Total.twbx

1260 KB

 
Chargement
Salesforce Help | Article