Understanding PERCENTILE.INC vs PERCENTILE.EXC in Power BI

[anmolmalviya05]

📊 What is a Percentile?

Percentiles divide your data into 100 equal parts. For example:

• The 25th percentile (Q1) indicates that 25% of the values fall below it.
• The 50th percentile (median) is the midpoint of your data.
• The 75th percentile (Q3) means 75% of the data lies below it.

But how do we calculate these in Power BI? 🤔

That’s where PERCENTILE.INC and PERCENTILE.EXC come in.

🧠 Quick Refresher: Quartiles & IQR

Imagine numbers from 1 to 100. Dividing them into four equal parts gives:

• Q1: 1–25
• Q2: 26–50
• Q3: 51–75
• Q4: 76–100

Now, the Interquartile Range (IQR) measures the middle 50% of your data.

🧮 Formula: 

IQR = Q3 − Q1

📌 Example:

• Q1 = 52
• Q3 = 58
• IQR = 58 − 52 = 6

Values that are more than 1.5 × IQR above Q3 or below Q1 are considered outliers.

🔍 What is PERCENTILE.EXC?

PERCENTILE.EXC means Exclusive of range endpoints.

It excludes 0 and 1 in its calculation, so: 

0 < k < 1

✅ Syntax: PERCENTILE.EXC(<column>, <k>)

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

This returns the 25th percentile excluding the boundaries of your dataset.

⚠️ Key Points about PERCENTILE.EXC:

• Returns BLANK if the column is empty.
• Returns error if k ≤ 0 or k ≥ 1.
• Interpolates if k falls between two values.
• Not defined for very small datasets (like 1-2 rows).

🔍 What is PERCENTILE.INC?

PERCENTILE.INC means Inclusive of range endpoints.

It includes 0 and 1, so: 0 ≤ k ≤ 1

✅ Syntax: PERCENTILE.INC(<column>, <k>)

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

This returns the 25th percentile including the full data range.

🛠️ Practical Implementation in Power BI

Let’s say you want to calculate:

• 25th percentile (Q1)
• 75th percentile (Q3)
• Interquartile Range (IQR)

Sample DAX Measures:

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

Q3 = PERCENTILE.INC(Sales[Sales Amount], 0.75)

IQR = [Q3] - [Q1]

Or for PERCENTILE.EXC:

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

Q3_Exc = PERCENTILE.EXC(Sales[Sales Amount], 0.75)

IQR_Exc = [Q3_Exc] - [Q1_Exc]

Visualize this using a box plot to spot trends and outliers quickly!

✅ Conclusion

Understanding PERCENTILE.INC and PERCENTILE.EXC allows you to:

• Build statistically sound reports
• Detect outliers
• Enhance data storytelling in Power BI

Both functions are powerful, and choosing the right one depends on your data scenario.

Regards

Anmol Malviya

Sr. Data Analyst | Addend Analytics