Daily DAX : Day 326 PERCENTILE.EXC

PERCENTILE.EXC DAX Function

The PERCENTILE.EXC function in Power BI DAX calculates the k-th percentile of values in a column, where k is a value between 0 and 1 (exclusive). It is used to determine the value below which a given percentage of data points fall, excluding the lowest and highest values in the interpolation.

Syntax

PERCENTILE.EXC(<column>, <k>)
    

• <column>: A column containing numeric values.
• <k>: A number between 0 and 1 (exclusive, e.g., 0.25 for 25th percentile).

Key Points

• Returns the value at the specified percentile using linear interpolation.
• Excludes the minimum and maximum values in the calculation (unlike PERCENTILE.INC).
• Ignores non-numeric values and blanks.
• Commonly used in statistical analysis to understand data distribution.

Use Case Example

Scenario: A retail company wants to analyze sales performance across stores. They want to identify the sales amount below which 90% of stores fall (90th percentile) to set performance benchmarks.

DAX Formula:

Sales_90th_Percentile = PERCENTILE.EXC(Sales[Revenue], 0.9)
        

Explanation: This formula calculates the revenue value where 90% of stores have lower sales. For example, if the result is $50,000, it means 90% of stores have sales below $50,000. This helps identify top-performing stores and set targets.

Notes

• Use PERCENTILE.EXC when you want to exclude extreme values in percentile calculations.
• Ensure the column contains sufficient numeric data to avoid errors.
• For inclusive percentiles (including min/max), use PERCENTILE.INC.