BottomPercent (MDX)

Returns a ascending sorted 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
percent	numeric-expression	one
measure	value-expression	one

Return

set

Description

Function sorts the set in the ascending 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 lowest value is returned.

Examples

BottomPercent(set,percent,numeric)

Returns an ascending sorted set which cumulative percent is more or equal than the specified 'percent' value.

In our example we return the first two members [20],[30] as their value is below the requested 45% of the total and with one member less it is not enough to reach 55%.

Query

WITH
    MEMBER [Geography].[Geo].[20]     AS 20
    MEMBER [Geography].[Geo].[30]     AS 30
    MEMBER [Geography].[Geo].[50]     AS 50
SELECT
    BottomPercent( {[Geography].[Geo].[20],[Geography].[Geo].[30],[Geography].[Geo].[50]},45, [Measures].[Amount] ) ON 0
FROM
    [Sales]

Result

20	30
20	30

Assertion : MDX Equals

WITH
    MEMBER [Geography].[Geo].[20]     AS 20
    MEMBER [Geography].[Geo].[30]     AS 30
SELECT
    {[Geography].[Geo].[20],[Geography].[Geo].[30]} ON 0
FROM
    [Sales]

Assertion : MDX Equals

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

BottomPercent(set,percent,numeric) - duplicated members

20	20
20	20

Special Examples (errors, null, ...)

BottomPercent(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,BASC) 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
    BottomPercent( {[Geography].[Geo].[E],[Geography].[Geo].[20],[Geography].[Geo].[30],[Geography].[Geo].[50]},150, [Measures].[Amount] ) ON 0
FROM
    [Sales]

Result

E	20	30	50
	20	30	50

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], BASC)  ON 0
FROM
    [Sales]

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

E

BottomPercent(set,percent,numeric) - negative percent

BottomPercent(set,percent)

XMLA Properties

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

BottomPercent (MDX)

Parameters

Return

Description

See Also

Examples

Special Examples (errors, null, ...)

XMLA Properties