Dax Function: PERCENTILEX.EXC
Category: Statistical Functions
The PERCENTILEX.EXC function in Power BI is a DAX function used to calculate the exclusive kk-percentile of a dataset based on a dynamic table or expression. Unlike PERCENTILEX.INC, this function excludes boundary values, making it ideal for statistical analysis where such exclusions are necessary.
Purpose of the Function
Determine the k-percentile of a dataset, excluding boundary values.
Type of Calculations
Performs statistical calculations to analyze the distribution of values.
Practical Use Cases
- Calculating percentile rankings for dynamic subsets of data.
- Identifying outliers by comparing values against percentile thresholds.
- Defining benchmarks in datasets where extreme values need to be excluded.
PERCENTILEX.EXC(<table>, <expression>, <k>)</k></expression></table>
| Parameter | Type | Description |
|---|---|---|
<Table> | Table | The table over which the percentile calculation is performed. |
<Expression> | Scalar | The expression to evaluate for each row of the table to determine the dataset. |
<K> | Scalar | A numeric value between 0 and 1 (exclusive) that represents the desired percentile. |
How Does PERCENTILEX.EXC Dax Works
Mathematical Principle
For each row in the table, evaluate the
<Expression>, producing a dataset.Sort the dataset in ascending order.
Calculate the position using the formula:
n: Number of values in the dataset.
k: Percentile parameter.
Exclude boundary values:
If k = 0 or k = 1, the function returns an error as boundary percentiles are not allowed.
Use interpolation for fractional positions.
Example:
For n = 6, :
Sorted dataset: [15, 20, 25, 30, 35, 40]
Position = ( 6 + 1 ) ⋅ 0.4 = 2.8 (6 + 1)
Result = Interpolated between the 2nd (20) and 3rd (25) values.
What Does It Return?
Returns a scalar value representing the exclusive k-percentile of the evaluated expression across the table.
When Should We Use It?
Exclusion of Boundaries: For scenarios where minimum and maximum values should not influence the calculation.
Dynamic Percentile Analysis: When percentile calculations rely on expressions and filtered datasets.
Custom Benchmarks: Establishing thresholds for middle ranges of data while avoiding extremes.
Examples
Basic Usage :
Find the 80th percentile of sales, excluding boundary values:
PERCENTILEX.EXC(Sales, Sales[Amount], 0.8)
Output: Returns the sales amount below which 80% of non-boundary sales values fall.
Column Usage
Calculate the percentile for dynamic product performance:
ADDCOLUMNS(
Products,
"80th Percentile",
PERCENTILEX.EXC(Sales, Sales[Amount], 0.8)
)
Use Case: Adds a column displaying the 80th percentile of sales for each product, excluding extremes.
Advanced Usage
Combine with filters for conditional percentile calculations:
CALCULATE(
PERCENTILEX.EXC(Sales, Sales[Amount], 0.25),
Sales[Category] = "Electronics"
)
Use Case: Calculates the 25th percentile for sales in the “Electronics” category.
Tips and Tricks
Valid Input: Ensure k is strictly between 0 and 1 to avoid errors.
Boundary Handling: Use
PERCENTILE.INCif boundary inclusion is necessary.Large Datasets: Preprocess data or filter subsets for better performance on extensive datasets.
Performance Impact of PERCENTILEX.EXC DAX Function:
Large datasets can slow performance. Optimize by:
Using smaller, filtered tables.
Pre-aggregating values in the
<Expression>.
Avoid recalculating static percentiles unnecessarily.
Related Functions You Might Need
PERCENTILEX.INC: Includes boundary values in the percentile calculation.
PERCENTILE.EXC: Operates on static column data rather than dynamic expressions.
RANKX: For calculating rankings in a dataset.
Want to Learn More?
For more information, check out the official Microsoft documentation for PERCENTILEX.EXC You can also experiment with this function in your Power BI reports to explore its capabilities.
Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.
It calculates the k-percentile of a dataset, excluding boundary values, based on a dynamic table or expression.
PERCENTILEX.EXC excludes boundary values, whereas PERCENTILEX.INC includes them in calculations.
No, the evaluated expression must return valid numeric results.
The function returns an error because boundary values are excluded.
Use filters, aggregations, or summarize data to reduce the calculation’s complexity.