Percentile (MDX+)

Returns the percentile value of the specified set expression

Parameters

Name	Type	Cardinality	Default value
	set-expression	one
	value-expression	one
	numeric-expression	one

Return

value

Description

The percentile function calculates the percentile value of the non-empty values (Wikipedia).

The parameters use a range from 0 to 100.0 where the corresponding value for 50.0 is the median value.

Percentile ( [Countries], [Sales Amount], 50.0 ) ; same as the Median

And it is also equivalent than using a function on a Vector :

Vector( [Countries], [Sales Amount], EXCLUDEEMPTY)->percentile( 50 )

Examples

Percentile(set,20)

Percentile(set,50) - same as Median

Percentile(set) - empty values

Percentile Amount
3.0

Special Examples (errors, null, ...)

Percentile(NULL), Percentile({})

Returns an empty cell if applied to an empty set or a NULL entity.

Query

WITH
    MEMBER [Measures].[Percentile Amount] AS Percentile( null, 1, 50 )
SELECT
    [Measures].[Percentile Amount] ON 0
FROM
    [Sales]

Result

Percentile Amount

Percentile Amount

Assertion : Cell Equals

WITH
    MEMBER [Measures].[Percentile Amount] AS Percentile({}, 2, 90)
SELECT
    [Measures].[Percentile Amount] ON 0
FROM
    [Sales]

Assertion : Cell Equals

WITH
    MEMBER [Measures].[Percentile Amount] AS null
SELECT
    [Measures].[Percentile Amount] ON 0
FROM
    [Sales]

Since Version

6.1

XMLA Properties

Find here the value of the XMLA properties when not specified otherwise.