PERCENTILEX.INC
A percentile of an expression over a table's rows, k from 0 to 1.
Group: Statistics. Compatible with DAX.
Syntax
PERCENTILEX.INC ( <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 0 to 1. |
Returns
Number. The k-th percentile of the expression's results.
Remarks
- The smallest result sits at k = 0 and the largest at k = 1; between two results the answer is on the straight line joining them (R's quantile of type 7).
- A blank result takes part as a zero that sorts before the real zeros, as in
MEDIANX; when the percentile lands on a blank, the answer is BLANK. Filter blanks out first when they should not count. - A k outside 0 to 1 is an error. Over an empty table the result is BLANK.
Examples
Three percentiles of five values
EVALUATE
VAR Liters = DATATABLE ( "Liters", INTEGER, { { 10 }, { 20 }, { 30 }, { 40 }, { 50 } } )
RETURN
ROW (
"k = 0", PERCENTILEX.INC ( Liters, [Liters], 0 ),
"k = 0.9", PERCENTILEX.INC ( Liters, [Liters], 0.9 ),
"k = 1", PERCENTILEX.INC ( Liters, [Liters], 1 )
)| k = 0 | k = 0.9 | k = 1 |
|---|---|---|
| 10 | 46 | 50 |
A blank result counts as zero
The blank sorts first, as a zero: at k = 0 the answer is that blank, and at 0.1 it is 0.4 of the way from zero to 10.
EVALUATE
VAR Liters = DATATABLE ( "Liters", INTEGER, { { 10 }, { BLANK () }, { 30 }, { 40 }, { 50 } } )
RETURN
ROW (
"k = 0", PERCENTILEX.INC ( Liters, [Liters], 0 ),
"k = 0.1", PERCENTILEX.INC ( Liters, [Liters], 0.1 ),
"k = 0.5", PERCENTILEX.INC ( Liters, [Liters], 0.5 )
)| k = 0 | k = 0.1 | k = 0.5 |
|---|---|---|
| (blank) | 4 | 30 |
A month of sales that only one month in ten beats
EVALUATE
ROW ( "90th percentile", PERCENTILEX.INC ( VALUES ( Sales[Month] ), [Liters sold], 0.9 ) )| 90th percentile |
|---|
| 59994.5 |