Tableau - Percentiles in Tableau

To find percentiles in tableau, right click on the measure and select percentile:

The drop down gives you several percentiles to choose from. By selecting Edit in Shelf (4th from the bottom) you can change to percentile to any value between 0 and 1.

Definition: The nth percentile is a value such that n percent of observations fall below that value.

Formula: The equation that tableau uses to calculate percentiles is two steps:

Step 1: Get the rank in the ordered set:

Step 2: Use the rank from step 1 above to get value of the element in the set associated with the rank.

Step 3: Use linear interpolation if needed when the rank is not a whole number.

Common percentiles are:

• 25th percentile (first quartile)
• 50th percentile (median)
• 75th percentile (third quartile)

Note: There are many formulas for percentiles. Tableau uses the same formula as Excel's PERCENTILE.INC function

Example:

Use the table below to work out the 20th, 67th and 95th percentiles for the following test scores:

20th percentile: Using the formula above:

                               nth percentile: 0.2
                               number of elements in set: 10
                               formula for rank: 0.2(10-1) + 1 = 2.8 
                               intuition: rank is between 2 and 3 so 20th percentile is between 55 and 60
                               linear interpolation:: 55 + 0.8(60-55) = 59
                               Conclusion:: 20th percentile is 59. 20% of test scores fall below 59.
                                 

67th percentile: Using the formula above:

                               nth percentile: 0.67
                               number of elements in set: 10
                               formula for rank: 0.67(10-1) + 1 = 7.03 
                               intuition: rank is between 7 and 8 so 67th percentile is between 80 and 91
                               linear interpolation:: 80 + 0.03(91-80) = 80.33
                               Conclusion:: 67th percentile is 80.33. 67% of all values fall below this.
                               

95th percentile: Using the formula above:

                               nth percentile: 0.95
                               number of elements in set: 10
                               formula for rank: 0.95(10-1) + 1 = 9.55
                               intuition: rank is between 9 and 10 so 95th percentile is between 94 and 97
                               linear interpolation:: 94 + 0.55(97-94) = 95.65
                               Conclusion:: 95th percentile is 95.65. 95% of all test values fall below this.
                               

Word of caution: Percentiles are not percentages. Percentiles determines where a particular value stands in the data set, or would stand if such a value existed. Percentiles are a measure to determine where a value stands relative to other values.

Say everyone on a test has scored above 90%, but you score exactly 90%, then you would have done the worst on this test. In this case, your 90% test score would be the 0th percentile because nobody scored worse than you.

Now let's say your prof gives a super hard exam, and you score 50%. If everyone else scores below 50% then your test score of 50% is in fact the 100th percentile since nobody score better than you!