Cubing — BottomPercent function (MDX)

The BottomPercent function sorts a set and returns the lowest-valued tuples in the set that sum to a specified percent of the total of the values of the set.

Returns: A set of tuples.

Syntax


>>-BottomPercent--(--SetExpression--,--Percentage--+--------------------------+--)-><
                                                   '-,--+-NumericExpression-+-'      
                                                        '-StringExpression--'

Parameters

SetExpression: Specifies the set whose lowest values are to be found.
Percentage: Specifies the percentage of the total of the values in the set, that the sum of the returned values will not exceed. For example, if you specify ten percent, the values that the function returns will sum to at most ten percent of the sum of every value in the set.
NumericExpression: Specifies a numeric expression that, when applied to each tuple in the set, gives the value by which the set is to be sorted. This expression is typically a measure. If you omit both this parameter and StringExpression, the function sorts the set based on the current value for each tuple.
StringExpression: Specifies an expression that resolves to a string representation of a number. When applied to each tuple in the set, the expression gives the value by which the set is to be sorted. This expression is typically a measure. If you omit both this parameter and NumericExpression, the function sorts the set based on the current value for each tuple.

Description

The BottomPercent function always breaks the hierarchy. The members of the set are sorted without regard to their positions in the hierarchy.

Example

In this example, you want to examine the stores that represent the bottom 10% of profit amount. The following query uses the BottomPercent function to return the profit amount of the stores that have profit amounts that represent the bottom 10% of your stores in ascending order of profit amount.

Query

SELECT {[Price Analysis].[Measures].[Profit Amount]} ON AXIS(0), 

       {BottomPercent(Descendants([Price Analysis].[Store].[All Stores], 

			 -1, LEAVES), 10, 

       [Price Analysis].[Measures].[Profit Amount])} ON AXIS(1)

FROM [Price Analysis]

Result

Store	Profit Amount
ValueTrend Store 554	56360.49
ValueTrend Store 835	58189.07

Related functions

You can use the TopPercent function to find the largest values in a set that compose a certain percent of the total of the values of the set.

You can use the BottomCount function to find a specified number of the smallest values in a set; or you can use the BottomSum function to find the smallest values in a set that compose a certain sum.

The BottomPercent function sorts sets in the same way that the Order function does with the BDESC flag specified: that is, the function breaks hierarchies and sorts in descending order.