Loading

Calculating Rank without Table Functions

Дата публикации: Sep 25, 2025
Задача
How to compute rank without table functions. For example, if the end user can drill down from [Category] to [Subcategory] the ranks should not change.
Действия
There are two possible workarounds:

Option 1: Use a level of detail expression inside of the RANK_DENSE() function

For detailed directions, please see Calculating Rank At a Level Other Than the Highest Level of Granularity
Note: with this option, categories that tie will always be assigned the same rank. Also, for some more complex views the table calculations may still become invalid when adding or removing dimensions from the view.

Option 2: Manually calculate the measure values for every ranking level

The attached modified workbook demonstrates the following directions:
  1. Create a calculated field with a name like "Highest Sales Values" with a calculation similar to the following:
    { FIXED : MAX( { INCLUDE [Segment], [Category] : SUM([Sales] ) } ) }
  2. Create a calculated field with a name like "2nd Highest Sales Value" with a calculation similar to the following:
    { FIXED : MAX( { INCLUDE [Segment], [Category]:
    	IF SUM([Sales]) < SUM([Highest Sales Values])
    	THEN SUM([Sales])
    	END
    	})}
  3. Repeat step 2 for as many rank levels as desired, replacing [Highest Sales Values] with the lowest value above the current rank.
  4. Create a calculated field with a name like "Rank (just LOD)" with a calculation similar to the following:
    IF { FIXED [Segment], [Category] : SUM([Sales])} = [Highest Sales Values]
    	THEN 1
    	ELSEIF { FIXED [Segment], [Category] : SUM([Sales])} = [2nd Highest Sales Value]
    	THEN 2
    	ELSEIF { FIXED [Segment], [Category] : SUM([Sales])} = [3rd Highest Sales Value]
    	THEN 3
    	END
Дополнительные ресурсы

To voice your support for the inclusion of this enhancement in a future product release, add your vote to the following Idea: Ranking with Level of Detail

Номер статьи базы знаний

001453776

Вложения

rank without table calcs.twbx

1180 KB

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