BottomSum

Returns the smallest possible subset of a set for which the total results of a numeric evaluation are at least a given sum. Elements of the result set are listed from smallest to largest.

Syntax

BottomSum ( set, numeric_value_expression, numeric_value_expression )

Parameter	Description
set	The set from which the lowest-summing elements are selected.
numeric_value_expression1	The given sum (see MDX Grammar Rules).
numeric_value_expression2	The numeric evaluation (see MDX Grammar Rules).

Notes

If the total results of the numeric evaluation do not add up to the given sum, an empty set is returned.
This function ignores negative and missing values.

Example

The following query selects Qtr1 and Qtr2 sales for the lowest selling products in Qtr1 (where Sales totals at least 10000).

SELECT
 {[Year].[Qtr1], [Year].[Qtr2]} 
ON COLUMNS,
 {
  BottomSum(
  [Product].Members, 10000, [Year].[Qtr1]
  ) 
 }
ON ROWS
FROM Sample.Basic
WHERE ([Measures].[Sales])

This query returns the grid:

(axis)	Qtr1	Qtr2
200-40	2807	2922
100-30	3187	3182
400-30	3763	3962
300-20	4248	4638