Returns: A set.
Syntax
>>-TopSum--(--SetExpression--,--Sum--+--------------------------+--)->< '-,--+-NumericExpression-+-' '-StringExpression--'
Parameters
- SetExpression
- Specifies the set whose highest values are to be found.
- Sum
- Specifies the number that the sum of the returned values must exceed.
- 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 TopSum function always breaks the hierarchy; that is, the members of the set are always sorted without regard to their positions in the hierarchy.
Example
In this example you want to see which stores have the highest profit amounts that make up 100000 in profits. The following query uses the TopSum function to return the profit amount of stores that have the largest profit amounts whose total is at least 100000. The stores are organized in descending order of profit amount.
SELECT {[Price Analysis].[Measures].[Profit Amount]}
ON AXIS(0),
{TopSum(Descendants([Price Analysis].[Store].[All Stores], -1, LEAVES),
100000,
[Price Analysis].[Measures].[Profit Amount])}
ON AXIS(1)
FROM [Price Analysis]
| Store | Profit amount |
|---|---|
| ValueTrend Store 1199 | 84813.24 |
| ValueTrend Store 116 | 81950.42 |
Related functions
You can use the BottomSum function to find the smallest values in a set that compose a certain sum.
You can use the TopCount function to find a specified number of the largest values in a set, or 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.
The TopSum 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.