The PERCENTILEX.EXC function returns the value corresponding to the k-th percentile of the values taken by an expression that is evaluated for all the rows of a table, k belonging to the open interval (0, 1).
PERCENTILEX.EXC(
table,
expression,
k
)
- table: Table containing the rows on which the expression will be evaluated. It can be the name of an existing table or an expression that returns a table.
- expression: Expression to evaluate for each row of the table.
- k: Percentile in the open interval (0, 1).
The PERCENTILEX.EXC function returns a real number.
If the column is empty, the function returns Blank.
If k is not in the open interval (0, 1), the function returns an error.
If k is not a multiple of 1 / (n + 1) (where n is the number of elements), PERCENTILEX.EXC performs an interpolation to calculate the value corresponding to the k-th percentile.
If we start from the following data table, Números:
...we can calculate what value is in the 0.2 percentile with the following measure:
p = PERCENTILEX.EXC('Números', 'Números'[N], 0.2)
In this case, the value 0.2 is not an integer multiple of 1 / (n + 1), so the function performs an interpolation.
As mentioned, table can be the name of a table or an expression that returns a table. To test it, we are going to feed the function with an expression of this type:
p = PERCENTILEX.EXC(FILTER('Números', [N]>4), 'Números'[N], 0.2)
We are applying the function to the subset of the Números table formed by those values greater than 4. The result is the one that can be seen below:
