Percentile functions are used when you want to find out the value in which a certain percentage of observations lie below. For example, the 90th percentile is a value where 90 percent of the observations in the data set or group are below. The 50th percentile is also know as the median or middle value where 50 percent of observations fall below.
There are two main percentile functions in Power BI:
The first parameter is the column which you want the percentile value for.
The second parameter is the kth percentile where k percentage of values will fall below.
Both formulas use a slightly different algorithm. The second algorithm works for any value of k between 0 and 1 (the 0th and 100th percentile). In the EXC version the data excludes both lower and upper bounds, while INC includes them. For large sample sizes, both algorithms will give similar results.
Define the following measures in the Power BI formula bar.
20th_PercentileExc = PERCENTILE.EXC(TransactionData[Revenue],0.2)
20th_PercentileInc = PERCENTILE.INC(TransactionData[Revenue],0.2)
While you can define percentile measures above, the analytics feature enables you to show percentiles across groups specified along a specific axis.
Add percentile lines to monitor daily revenue
This graph shows 11 product Ids sorted by revenue with a max line, a min line and a 90th percentile line. A max line is the 100th percentile line while the min line represents the 0th percentile. Power BI uses INC version of the percentile formula. It is no coincidence that the 90th percentile line has the exact same value as the total revenue from candy H (It sits right on top of candy H). The 80th percentile line if plotted would sit right on top of Candy I, the 70th percentile on top of Candy D and so on. The formula used for calculating the INC percentile is:
$$ {k(n-1) + 1} $$
Where k is the percentile and n is the number of items that are ordered.
This states the rank when the values are ordered from smallest to largest.
For example, using ${k = 0.9 }$ and ${n = 11}$ in the formula returns a rank of 10. Candy A is the smallest value, so it has a rank of 1. Candy K is the largest value which has a rank of 11. A rank of 10 corresponds to the revenue value of Candy H, which is the 90th percentile.