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!