Percentile.Inc Vs Percentile.Exc in Power BI

  • Published
  • Posted in Power BI
  • Updated
  • 3 mins read

Hi there, in this blog we are going to learn about Percentile function in PowerBI. 
Let’s look at the basic data below: 

Before we start, I would like to mention about Quartile. What exactly it is? Let’s say we have numbers from 1-100. If we divide it into 4 quarters, every division will be of 25 units. Range will appear like 1-25, 26-50, 51-75 and 76-100. Now the median comes into picture, Notice the diagram below: 

The median of a set of data separates the set-in half. 

The median of the lower half of a set of data is the lower quartile ( LQ) or Q1 

The median of the upper half of a set of data is the upper quartile ( UQ ) or Q3 

The upper and lower quartiles can be used to find another measure of variation call the interquartile range  

The interquartile range or IQR is the range of the middle half of a set of data. It is the difference between the upper quartile and the lower quartile. 

Interquartile range = Q3−Q1 

In the above example, the lower quartile is 5252 and the upper quartile is 58 . 

The interquartile range is 58−52 or 6 

Data that is more than 1.51.5 times the value of the interquartile range beyond the quartiles are called outliers.  

Percentile.EXC : EXC is Exclusive of Range 

Returns the kth percentile of values in a range, where 0 < k < 1. 

  • If the column is empty, BLANK () is returned. 
  • If k <= 0, or k >= 1, it is out of range and an error is returned. 
  • If k is non-numeric, an error is returned. 
  • If k is not a multiple of 1/(n + 1), PERCENTILE.EXC will interpolate to determine the value at the kth percentile. 

PERCENTILE.EXC will interpolate when the value for the specified percentile is between two values in the array. If it cannot interpolate for the k percentile specified, an error is returned. 

= PERCENTILE.EXC (Sales[Sales Amount],0.25) 

Percentile.INC: INC is Inclusive of Range 

 
Returns the kth percentile of values in a range, where 0 < = k < = 1. 

  • If the column is empty, BLANK () is returned. 
  • If k is non-numeric or outside the range 0 to 1, an error is returned. 
  • If k is not a multiple of 1 / (n + 1), PERCENTILE.INC will interpolate to determine the value at the kth percentile. 

PERCENTILE.INC will interpolate when the value for the specified percentile is between two values in the array. If it cannot interpolate for the k percentile specified, an error is returned. 

Example 

= PERCENTILE.INC (Sales[Sales Amount],0.25)  

Practical Implementation: Observe the difference below: 

Hope you understood the difference. Thanks for reading. 

Dhiraj Kala 
Data Analyst
Addend Analytics

Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S.

Addend has successfully implemented 100+ Microsoft Power BI and Business Central projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia.

Get a free consultation now by emailing us or contacting us.

Email Us Or Contact Us