Introduction
RANK is a table calculation which computes the ranking of each value in a partition (in a table). The RANK function exists in different variants which include, RANK, RANK_DENSE, RANK_MODIFIED, RANK_UNIQUE & RANK_PERCENTILE. In this short article, I will be exploring each RANK variant, giving an example on how each behaves when used in a calculation.
(Note: NULL values are ignored when ranking functions).
And, when the order of ranking isn’t specified (as either ascending or descending), then descending order is applied as the default order.
RANK Function
This function returns the standard competition rank for the current row in a partition. Where, identical values are assigned an identical rank. For example, with this function, this set of values (4,10,15,15,20) would be ranked (5,4,2,2,1) – when ranking is in descending order.
Example: RANK(SUM([Sales])) – computes the competition rank in descending order by default, as the order isn’t specified.
RANK_DENSE Function
This function returns the dense rank for the current row in a partition. Where, identical values are given the same rank (that is the next number in the ranking sequence), with the next value after duplicated values being computed as though the duplicate values were single values.
For example, with this function, this set of values (4,10,15,15,20) would be ranked (4,3,2,2,1) – when ranking is in descending order.
Example: RANK_DENSE(SUM([Sales]),’asc’) – computes the dense rank in ascending order as specified in the calculation.
RANK_MODIFIED Function
This function returns the modified competition rank for the current row in a partition. Where, identical values are assigned an identical rank, in a modified way as shown in the example below.
With this function, this set of values (4,10,15,15,20) would be ranked (5,4,3,3,1) – when ranking is in descending order.
The same set of values (4,10,15,15,20) would be ranked (1,2,4,4,5) – when ranking is in ascending order.
Example: RANK_MODIFIED(SUM([Sales]),’desc’) – computes the modified competition rank in descending order as specified in the calculation.
RANK_UNIQUE Function
This function returns the unique rank for the current row in the partition. Where, identical values are assigned different ranks.
For example, with this function – this set of values (4,10,15,15,20) would be ranked (5,4,3,2,1) – when ranking is in descending order.
The same set of values (4,10,15,15,20) would be ranked (1,2,3,4,5) – when ranking is in ascending order.
Example: RANK_UNIQUE(SUM([Sales])) - computes the unique rank in descending order by default, as the order isn’t specified.
RANK_PERCENTILE Function
This function returns the percentile rank for the current row in the partition.
For example, with this function – this set of values (4,10,15,15,20) would be ranked (1.00,0.75,0.50,0.50,0.00) – when ranking is in descending order.
The same set of values (4,10,15,15,20) would be ranked (0.00,0.25,0.75,0.75,1.00) – when ranking is in ascending order.
Example: RANK_PERCENTILE(SUM([Sales]),’asc’) – computes the percentile rank in ascending order as specified in the calculation.
Tableau RANK function in summary
In summary, if you’re to apply these variants of rank function - both in ascending and descending order to this sample data – this is what you’ll get.
Computing the ranks in descending order
Computing the ranks in ascending order
I hope this article was helpful to you. To receive more of the Tableau tips and tricks, kindly join our mailing list by subscribing below.
Thank you for reading.
