Daily DAX : Day 307 PERCENTILE.EXC

PERCENTILE.EXC DAX Function

Description

The PERCENTILE.EXC function in Power BI DAX calculates the k-th percentile of a column of values, excluding the 0th and 100th percentiles. It returns the value at a specified percentile rank, useful for statistical analysis.

Syntax

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

• <column>: A column containing numeric values.
• <k>: A number between 0 and 1 (exclusive), representing the percentile rank (e.g., 0.9 for 90th percentile).

Use Case

PERCENTILE.EXC is used to identify thresholds or benchmarks in datasets, such as determining the value below which a certain percentage of data falls. Common scenarios include:

• Finding the 90th percentile of sales to identify top-performing products.
• Analyzing test scores to determine high or low performance thresholds.
• Setting performance benchmarks, e.g., salary ranges or response times.

Example

Suppose you have a table Sales with a column Revenue. To find the 90th percentile revenue:

90th Percentile Revenue = PERCENTILE.EXC(Sales[Revenue], 0.9)
    

This returns the revenue value below which 90% of the data falls.

Notes

• k must be between 0 and 1, excluding 0 and 1.
• The function interpolates between values if the percentile falls between data points.
• Use PERCENTILE.INC if you need to include 0th and 100th percentiles.