PERCENTILE.EXC DAX Function (Statistical)
Returns the k-th (exclusive) percentile of values in a column.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| Column |
A column containing the values. |
|
| K |
Desired percentile value in the interval [1/(n+1),1-1/(n+1)], where n is a number of valid data points. |
Return values
Percentile value.
Remarks
PERCENTILEX considers the blank values returned by Expression. This behavior is different from PERCENTILE, which ignores the blank values.
» 3 related functions
Examples
-- PERCENTILE.EXC computed the k-th percentile, exclusive
-- PERCENTILE.INC computed the k-th percentile, inclusive
--
-- Both functions rank the N values from 1 (lowest)
-- to N (highest), then determine the possibly-non-integer
-- calculated rank for the specified percentage argument K
-- (a decimal number between 0.00 and 1.00), and finally
-- use linear interpolation between the closest integer-rank
-- values of the data array.
--
-- For PERCENTILE.EXC the calculated rank is K*(N+1)
-- For PERCENTILE.INC the calculated rank is K*(N-1)+1
--
-- MEDIAN corresponds to PERCENTILE.INC with k=0.50
DEFINE
TABLE SampleData = { 2, 4, 4, 4, 5, 5, 7, 9 }
EVALUATE
{
( "AVERAGE", AVERAGE ( SampleData[Value] ) ),
( "MEDIAN", MEDIAN ( SampleData[Value] ) ),
( "PERCENTILE.EXC 0.25", PERCENTILE.EXC ( SampleData[Value], 0.25 ) ),
( "PERCENTILE.INC 0.25", PERCENTILE.INC ( SampleData[Value], 0.25 ) ),
( "PERCENTILE.EXC 0.50", PERCENTILE.EXC ( SampleData[Value], 0.50 ) ),
( "PERCENTILE.INC 0.50", PERCENTILE.INC ( SampleData[Value], 0.50 ) ),
( "PERCENTILE.EXC 0.75", PERCENTILE.EXC ( SampleData[Value], 0.75 ) ),
( "PERCENTILE.INC 0.75", PERCENTILE.INC ( SampleData[Value], 0.75 ) )
}
| Value1 | Value2 |
|---|---|
| AVERAGE | 5.00 |
| MEDIAN | 4.50 |
| PERCENTILE.EXC 0.25 | 4.00 |
| PERCENTILE.INC 0.25 | 4.00 |
| PERCENTILE.EXC 0.50 | 4.50 |
| PERCENTILE.INC 0.50 | 4.50 |
| PERCENTILE.EXC 0.75 | 6.50 |
| PERCENTILE.INC 0.75 | 5.50 |
--
-- Different handling of blanks between PERCENTILE and PERCENTILEX
-- PERCENTILE ignores blank values
-- PERCENTILEX considers blank values
--
DEFINE
TABLE SampleDataWithBlanks =
{ BLANK (), BLANK (), BLANK (), 1, 2, 3, 4 }
EVALUATE
{
( "PERCENTILE.EXC 0.25", PERCENTILE.EXC ( SampleDataWithBlanks[Value], 0.25 ) ),
( "PERCENTILE.EXC 0.50", PERCENTILE.EXC ( SampleDataWithBlanks[Value], 0.50 ) ),
( "PERCENTILE.EXC 0.75", PERCENTILE.EXC ( SampleDataWithBlanks[Value], 0.75 ) ),
( "PERCENTILEX.EXC 0.25", PERCENTILEX.EXC ( SampleDataWithBlanks, SampleDataWithBlanks[Value], 0.25 ) ),
( "PERCENTILEX.EXC 0.50", PERCENTILEX.EXC ( SampleDataWithBlanks, SampleDataWithBlanks[Value], 0.50 ) ),
( "PERCENTILEX.EXC 0.75", PERCENTILEX.EXC ( SampleDataWithBlanks, SampleDataWithBlanks[Value], 0.75 ) )
}
| Value1 | Value2 |
|---|---|
| PERCENTILE.EXC 0.25 | 1.25 |
| PERCENTILE.EXC 0.50 | 2.50 |
| PERCENTILE.EXC 0.75 | 3.75 |
| PERCENTILEX.EXC 0.25 | (Blank) |
| PERCENTILEX.EXC 0.50 | 1.00 |
| PERCENTILEX.EXC 0.75 | 3.00 |
Related articles
Learn more about PERCENTILE.EXC in the following articles:
-
Statistical Patterns
DAX includes a few statistical aggregation functions, such as average, variance, and standard deviation. Other typical statistical calculations require you to write longer DAX expressions. Excel, from this point of view, has a much richer language. The Statistical Patterns are a collection of common statistical calculations: median, mode, moving average, percentile, and quartile. » Read more
Related functions
Other related functions are:
Last update: Jan 31, 2023 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Ville-Pietari Louhiala
Microsoft documentation: https://docs.microsoft.com/en-us/dax/percentile-exc-function-dax