When I saw the data I immediately decided that I want to show how the countries’ rank by selling prices changed over years.

The calculation process was easy because Tableau offers 5 different table calculations:

Rank()

Rank_Dense()

Rank_Unique()

Rank_Modified()

Rank_Percentile()

Let’s look at each function one by one.

The rank functions compare numbers to other numbers in the same list.What is the difference between Rank and Rank_Dense? The difference is visible when there are duplicated records. I created an example dataset to illustrate the usage of rank calculations:

The Rank() function returns the position of a value within the partition of a dataset, with gaps in the ranking. As shown in the example above David, Lewitt and Sarah have the same number of marks (4).Using the Rank function they get the same rank(2) and the next highest order is ranked number 5. There is the gap (1,2,2,2,5) in the ranking.

while
the Rank_Dense() calculation also returns the position of a value within the partition of a dataset, without gaps in the ranking. David, Lewitt and Sarah are all ranked 2nd and Tamara is ranked 3rd because the Rank_Dense calculation ranks the values without gaps (1,2,2,2,3)

The Rank_Unique returns the distinct rank for each value.

Rank_Modified() also returns the position of a value with gaps in the ranking. The difference between this and Rank() calculation is while the Rank creates the rank from bottom to top, the Rank_Modified works from top to bottom when the value is duplicated. The highest value is ranked as 1 and the next three identical is 4. (1,4,4,4,5)

Rank_Percentile() calculation computes a percentile rank for each value in a partition.For example, Tamara’s Rank_Percentile value is 33%, so we know 67% of the people are over based on the marks and 33% are no higher in marks than Tamara.

Check out Alexander Mou’s post about this topic. He explains how these rank functions look as graphs and what are the relationships between the Rank() and Rank_Percentile() functions.