PERCENTILE

Description:

This function is used to return a scalar value signifying percentile of the given set.

Syntax:

Percentile(Set_Expression, Measure, Numeric Value, Inclusive/Exclusive flag)

Return type:

SCALAR

Parameters:

Parameter	Description

Parameter	Description
Set_Expression	It is a valid MDX expression that returns a set.
Measure	It is a valid measure.
Numeric Value	It is a valid numeric value in the range (0, 100].
Inclusive/Exclusive	Inclusive: Specifies the value at or below which the given percentage of values may be found in distribution. Exclusive: Specifies the value below which the given percentage of values may be found in distribution.

Example:

MEMBER [Measures].[25th Percentile] AS
        Percentile([Product].[Product].[Product].Members, [Measures].[Internet Sales Amount], 25)
    MEMBER [Measures].[50th Percentile] AS
        Percentile([Product].[Product].[Product].Members, [Measures].[Internet Sales Amount], 50)
    MEMBER [Measures].[75th Percentile] AS
        Percentile([Product].[Product].[Product].Members, [Measures].[Internet Sales Amount], 75)
    MEMBER [Measures].[90th Percentile] AS
        Percentile([Product].[Product].[Product].Members, [Measures].[Internet Sales Amount], 90)
    MEMBER [Measures].[IQR] AS
        [Measures].[75th Percentile] - [Measures].[25th Percentile]
SELECT {[Measures].[25th Percentile], [Measures].[50th Percentile], 
        [Measures].[75th Percentile], [Measures].[90th Percentile], [Measures].[IQR]} ON COLUMNS
FROM [AdventureWorksMF_ValueBased]