Loading

Implementing a Top N Filter While Maintaining Correct Percentages

Дата публикации: Apr 13, 2024
Описание
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.
Решение

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]) } )
Номер статьи базы знаний

001473605

Вложения

top_N_correct_percentage.twbx

457 KB

Top N LOD % of Total.twbx

1260 KB

 
Загрузка
Salesforce Help | Article