Understanding how statistical functions behave inside Power BI DAX is essential for anyone building high-quality analytical dashboards, predictive models, or enterprise-grade data insights. Among these functions, PERCENTILE.INC and PERCENTILE.EXC are two of the most frequently misunderstood and misused.
Both play a critical role in data distribution analysis, outlier detection, performance benchmarking, segmentation modeling, and advanced analytics, especially for organizations that rely on accurate percentile-based insights for decision-making.
Hi there, in this blog, we are going to learn about the Percentile function in Power BI.
Let’s look at the basic data below:
Before we start, I would like to mention Quartile. What exactly is it? 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 the 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:
Ready to upgrade your Power BI and Microsoft Fabric analytics?
Talk to our experts and discover how advanced DAX, AI-powered insights, and enterprise data modeling can transform your reporting efficiency.
Schedule a Free Consultation
Why Percentile Calculations Matter in Power BI
Percentile calculations are the backbone of modern analytical scenarios, especially in:
- Inventory optimization
- Customer segmentation
- Sales performance benchmarking
- Risk scoring models
- Pricing analytics
- Employee performance distribution
Both PERCENTILE.INC and PERCENTILE.EXC directly impacts:
- Data modeling accuracy
- Outlier detection
- Statistical reporting
- AI/ML preparation pipelines
- DAX-based analytical models
This makes understanding the difference between them essential for BI developers, financial analysts, and data scientists working inside Power BI.
Understanding Percentile.INC vs Percentile.EXC in Power BI
- PERCENTILE.INC (Inclusive Range)
PERCENTILE.INC includes both endpoints of the data range.
- Allows 0 to 1, including boundaries
- Works well for financial models, operational data, and business KPIs
- Typically produces a slightly smaller value for quartiles compared to EXC
2. PERCENTILE.EXC (Exclusive Range)
PERCENTILE.EXC excludes the minimum and maximum ranks.
- Accepts 0 < k < 1 only
- Aligns with strict mathematical definitions
- Often used in statistical modeling and academic datasets
Practical Real-World Differences
Let’s illustrate it with a business scenario:
Scenario:
You want to calculate the 25th percentile (Q1) of monthly sales.
Using PERCENTILE.INC(…, 0.25)
Best when:
- Data includes extreme values
- You want inclusive quartiles
- Business KPIs use standard inclusive ranges
Using PERCENTILE.EXC(…, 0.25)
Best when:
- Statistical rigor is required
- Outliers need stronger separation
- You’re preparing data for machine learning models
When Should You Use Which?
Use PERCENTILE.INC when:
- Building dashboards for CXOs
- Working with complete datasets
- Performing exploratory data analysis
- Outlier sensitivity is lower
Use PERCENTILE.EXC when:
- Doing academic-level statistical analysis
- Preparing data for ML algorithms
- You need precise percentile calculation without boundary bias
Why This Matters for Power BI Developers
Using the wrong percentile function can lead to:
- Misleading KPIs
- Incorrect segmentation
- Faulty performance benchmarks
- Poor forecasting accuracy
- Incorrect outlier removal
For enterprise analytics, even a small percentile miscalculation can:
- Impact inventory decisions
- Trigger wrong discount brackets
- Misclassify customers into tiers
- Influence workforce performance ratings
Struggling with data accuracy, performance issues, or complex DAX calculations?
Our team can help you build scalable, future-ready analytics pipelines that deliver real business impact. Speak With a Microsoft-Certified BI Specialist
Using Percentiles for Outlier Detection
Power BI users commonly combine percentile values with Interquartile Range (IQR) to remove outliers.
IQR = Q3 − Q1
Lower Bound = Q1 – (1.5 * IQR)
Upper Bound = Q3 + (1.5 * IQR)
Percentile.INC and Percentile.EXC provide Q1 and Q3 values, and choosing one changes which records are flagged as outliers.
Why Teams Choose Addend Analytics
At Addend Analytics, we implement:
- Advanced DAX modeling
- Microsoft Fabric enterprise analytics
- AI-driven data intelligence
- Predictive modeling with Azure ML
- Enterprise dashboard engineering
If you want to improve your organization’s analytical accuracy using Power BI, percentile models, or Microsoft Fabric, we can help you build the perfect solution.
Contact us: solutions@addendanalytics.com
