Export (0) Print
Expand All
Expand Minimize
0 out of 1 rated this helpful - Rate this topic

BottomSum (MDX)

Sorts a specified set in ascending order, and returns a set of tuples with the lowest values whose sum is equal to or less than a specified value.


BottomSum(Set_Expression, Value, Numeric_Expression)
Set_Expression

A valid Multidimensional Expressions (MDX) expression that returns a set.

Value

A valid numeric expression that specifies the value against which each tuple is compared.

Numeric_Expression

A valid numeric expression that is typically a Multidimensional Expressions (MDX) expression of cell coordinates that return a number.

The BottomSum function calculates the sum of a specified measure evaluated over a specified set, sorting the set in ascending order. The function then returns the elements with the lowest values whose total of the specified numeric expression is at least the specified value (sum). This function returns the smallest subset of a set whose cumulative total is at least the specified value. The returned elements are ordered smallest to largest.

Important noteImportant

The BottomSum function, like the TopSum function, always breaks the hierarchy.

The following example returns, for the Bike category, the smallest set of members of the City level in the Geography hierarchy in the Geography dimension for fiscal year 2003, and whose cumulative total, using the Reseller Sales Amount measure, is at least the sum of 50,000 (beginning with the members of this set with the smallest number of sales):

SELECT

[Product].[Product Categories].Bikes ON 0,

BottomSum

({[Geography].[Geography].[City].Members}

, 50000

, ([Measures].[Reseller Sales Amount],[Product].[Product Categories].Bikes)

) ON 1

FROM [Adventure Works]

WHERE([Measures].[Reseller Sales Amount],[Date].[Fiscal].[Fiscal Year].[FY 2003])

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.