TopPercent (MDX)

Returns a sorted (descending order) set which cumulative percent is greater or equal than the specified percent (i.e., a value between 0 and 100).

Parameters

Name	Type	Cardinality	Default value
set	set-expression	one
sum	numeric-expression	one	1
measure	value-expression	one

Return

set

Description

The function sorts the set in descending order according to the specified measure and returns the minimum number of elements whose cumulative percent is greater or equal to the specified percent.

If 0 was specified as a percent the member with the highest value is returned.

Examples

TopPercent(member-set,numeric,measure)

50	30
50	30

TopPercent(member-set,numeric,measure) - duplicated members

40	40
40	40

Special Examples (errors, null, ...)

TopPercent(member-set,percent,numeric) - percent > 100 and empty values

If the specified 'percent' value is greater or equal than 100% all members will be returned, including empty cells.

In this case, the function behaves as Order(set,numeric2,BDESC) function.

Query

WITH
    MEMBER [Geography].[Geo].[E]      AS NULL
    MEMBER [Geography].[Geo].[20]     AS 20
    MEMBER [Geography].[Geo].[30]     AS 30
    MEMBER [Geography].[Geo].[50]     AS 50
SELECT
    TopPercent( {[Geography].[Geo].[E],[Geography].[Geo].[20],[Geography].[Geo].[30],[Geography].[Geo].[50]},200, [Measures].[Amount] ) ON 0
FROM
    [Sales]

Result

50	30	20	E
50	30	20

Assertion : MDX Equals

WITH
    MEMBER [Geography].[Geo].[E]      AS NULL
    MEMBER [Geography].[Geo].[20]     AS 20
    MEMBER [Geography].[Geo].[30]     AS 30
    MEMBER [Geography].[Geo].[50]     AS 50
SELECT
     Order({[Geography].[Geo].[E],[Geography].[Geo].[20],[Geography].[Geo].[30],[Geography].[Geo].[50]}, [Measures].[Amount], BDESC)  ON 0
FROM
    [Sales]

TopPercent(set,percent,numeric) - percent equals 0

50
50

TopPercent(set,percent,numeric) - negative percent

TopPercent(member-set,numeric)

XMLA Properties

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