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:
Note: There are many formulas for percentiles. Tableau uses the same formula as Excel's PERCENTILE.INC function
Use the table below to work out the 20th, 67th and 95th percentiles for the following test scores:
Rank (low to high) | Test Scores |
---|---|
1 | 51 |
2 | 55 |
3 | 60 |
4 | 66 |
5 | 73 |
6 | 74 |
7 | 80 |
8 | 91 |
9 | 94 |
10 | 97 |
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!