In Tableau, when you choose 'Percentile' for a quick table calculation, the RANK_PERCENTILE function is used.
This can be verified by editing the table calculation.
In this blog, I'll explain what this table calculation is doing.
The lowest number is classified as 0%, the highest number as 100%, and the remaining numbers are ranked between the two. This function ranks the numbers starting from 0.
E.g. if the numbers were 9,12,10,6
the RANK_PERCENTILE function will rank these numbers as follows:
9 -> rank 1
12 -> rank 3
10 -> rank 2
6 -> rank 0
Consider the rank of the numbers as position numbers for the purpose of this explanation.
When Computing Across:
The table calculation is partitioned at every row when computing across, therefore the RANK PERCENTILE calculation is:
(1/(number of columns-1) ) * (position number)
The sales figures for the first row is ranked as follows:
£117,299 -> rank 0
£163,797 -> rank 1
£208,291 -> rank 2
£252,613 -> rank 3
First step of calculation -> (number of columns -1) = 4-1 = 3
Second step of calculation -> 1/3 = 0.333
Third step of calculation -> for £117,299 -> rank 0 -> 0.333 (0) = 0
for £163,797 -> rank 1 -> 0.333(1) = 0.333
for £208,291 -> rank 2 -> 0.333(2) = 0.667
for £252,613 -> rank 2 -> 0.333(3) = 1
These steps are repeated for each row.
If one of the numbers was repeated, for example, if there were two £163,797s instead of a £208,291, the rankings would be as follows:
£117,299 -> rank 0
£163,797 -> rank 2
£163,797 -> rank 2
£252,613 -> rank 3
as a result the third step of calculation would be:
for £117,299 -> rank 0 -> 0.333 (0) = 0
for £163,797 -> rank 2 -> 0.333(2) = 0.667
for £163,797 -> rank 2 -> 0.333(2) = 0.667
for £252,613 -> rank 2 -> 0.333(3) = 1
You can think of RANK_PERCENTILE as a function that ranks numbers similarly to RANK_MODIFIED, with the exception that it starts ranking at 0 instead of 1.
When Computing Down:
The table calculation is partitioned at every column when computing down, therefore the RANK_PERCENTILE calculation is:
(1/(number of rows-1) ) * (position number) like in the example below:
The sales figures for the first column is ranked as follows:
£163,797 -> rank 0
£167,026 -> rank 1
£170,146 -> rank 2
Since there are 3 rows:
First step of calculation -> (number of rows -1) = 3-1 = 2
Second step of calculation -> 1/2 = 0.5
Third step of calculation -> for £163,797 -> rank 0 -> 0.5 (0) = 0
for £167,026 -> rank 1 -> 0.5(1) = 0.5
for £170,146 -> rank 2 -> 0.5(2) = 1
These steps are then repeated for each column.
When Computing Across Then Down or Down Then Across:
Since the table calculation is not partitioned by anything when computing across then down or down then across, the RANK PERCENTILE calculation is:
(1/((number of rows * number of columns)-1) ) * (position number)
The sales figures will be ranked as follows:
£117,299 -> rank 0
£125,0651 -> rank 1
£148,772 -> rank 2
£163,797 -> rank 3
and so on...
First step of calculation -> (number of rows * number of columns) = 3*4 = 12
Second step of calculation -> 12-1 = 11
Third step of calculation -> 1/11 = 0.091
Fourth step of calculation -> for £117,299 -> rank 0 -> 0.091 (0) = 0
for £125,0651 -> rank 1 -> 0.091(1) = 0.091
for £148,772 -> rank 2 -> 0.091(2) = 0.182
for £163,797 -> rank 3 -> 0.091(2) = 0.273
and so on...
And that's how Tableau's RANKED_PERCENTILE table calculation works!