POWER BI: Modeling

Percentile Functions

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.

Percentile functions

There are two main percentile functions in Power BI:

• PERCENTILE.EXC(column, kth percentile)
• PERCENTILE.INC(column, kth percentile)

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.

Example Formulas:

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)
	            

Using the Percentile Measures Defined above:

Plotting percentiles in a graph, can reveal information about the distribution of the data. The figure above reveals the data is largely right skewed. The difference in revenue is much larger between the 80th percentile and the 50th percentile, compare to the 50th percentile and the 20 percentile. This is especially true on orders for Candy K.

Using the Analytics Feature

While you can define percentile measures above, the analytics feature enables you to show percentiles across groups specified along a specific axis.

Power BI Demo: Daily Revenue

Add percentile lines to monitor daily revenue

Power BI Demo: Closer Look at Percentile Formula

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.