PERCENTILEX.EXC
A percentile of an expression over a table's rows, k strictly between 0 and 1.
Group: Statistics. Compatible with DAX.
Syntax
PERCENTILEX.EXC ( <table>, <expression>, <k> )| Parameter | Type | Description |
|---|---|---|
table | Table | The rows to go through. |
expression | Expression evaluated for each row | The number worked out for each row. |
k | Number | The share of results below the answer, from 1/(n + 1) to n/(n + 1) for n rows. |
Returns
Number. The k-th percentile of the expression's results by the exclusive method.
Remarks
- The exclusive method sets the n sorted results at k = 1/(n + 1) … n/(n + 1) and draws straight lines between them; a k outside that range is an error.
- A blank result takes part as a zero that sorts before the real zeros. When the percentile lands on a blank, the answer is BLANK.
- Dates, text and TRUE/FALSE are errors.
Examples
Exclusive and inclusive at 0.25
EVALUATE
VAR Liters = DATATABLE ( "Liters", INTEGER, { { 10 }, { 20 }, { 30 }, { 40 }, { 50 } } )
RETURN
ROW (
"PERCENTILEX.EXC", PERCENTILEX.EXC ( Liters, [Liters], 0.25 ),
"PERCENTILEX.INC", PERCENTILEX.INC ( Liters, [Liters], 0.25 )
)| PERCENTILEX.EXC | PERCENTILEX.INC |
|---|---|
| 15 | 20 |
The lowest and highest k allowed
With five rows, k runs from 1/6 to 5/6: those two give the smallest and the largest value.
EVALUATE
VAR Liters = DATATABLE ( "Liters", INTEGER, { { 10 }, { 20 }, { 30 }, { 40 }, { 50 } } )
RETURN
ROW (
"k = 1/6", PERCENTILEX.EXC ( Liters, [Liters], 1 / 6 ),
"k = 5/6", PERCENTILEX.EXC ( Liters, [Liters], 5 / 6 )
)| k = 1/6 | k = 5/6 |
|---|---|
| 10 | 50 |
A blank result counts as zero
Sorted, the rows are blank, 10, 30, 40 and 50. At 0.25 the answer lies halfway between the blank (as zero) and 10; at 1/6 it is the blank itself.
EVALUATE
VAR Liters = DATATABLE ( "Liters", INTEGER, { { 10 }, { BLANK () }, { 30 }, { 40 }, { 50 } } )
RETURN
ROW (
"k = 0.25", PERCENTILEX.EXC ( Liters, [Liters], 0.25 ),
"k = 1/6", PERCENTILEX.EXC ( Liters, [Liters], 1 / 6 )
)| k = 0.25 | k = 1/6 |
|---|---|
| 5 | (blank) |