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 largest to smallest.
Syntax
TopSum ( set, numeric_value_expression1, numeric_value_expression2 )
Parameter | Description |
---|---|
The set from which the highest-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 the top-selling markets that collectively contribute 60,000 to the total yearly Diet products sales, and displays the quarterly sales for each Diet product.
SELECT CrossJoin( [Product].[Diet].Children, [Year].Children ) ON COLUMNS, TopSum( [Market].Levels(0).Members, 60000, [Product].[Diet] ) ON ROWS FROM Sample.Basic WHERE ([Scenario].[Actual], [Measures].[Sales])
This query returns the grid:
(axis) | 100-20 | 100-20 | 100-20 | 100-20 | 200-20 | 200-20 | 200-20 | 200-20 | 300-30 | 300-30 | 300-30 | 300-30 |
---|---|---|---|---|---|---|---|---|---|---|---|---|
(axis) | Qtr1 | Qtr2 | Qtr3 | Qtr4 | Qtr1 | Qtr2 | Qtr3 | Qtr4 | Qtr1 | Qtr2 | Qtr3 | Qtr4 |
Illinois | 755 | 958 | 1050 | 888 | 1391 | 1520 | 1562 | 1402 | 675 | 755 | 859 | 894 |
California | 367 | 491 | 506 | 468 | 1658 | 1833 | 1954 | 1706 | 700 | 802 | 880 | 673 |
Colorado | 700 | 802 | 880 | 673 | 549 | 465 | 412 | 539 | 1006 | 921 | 892 | 991 |
Washington | 637 | 712 | 837 | 704 | 459 | 498 | 597 | 514 | 944 | 799 | 708 | 927 |
Iowa | 162 | 153 | 121 | 70 | 129 | 129 | 129 | 129 | 1658 | 1833 | 1954 | 1706 |
Florida | 620 | 822 | 843 | 783 | 548 | 611 | 657 | 577 | 332 | 323 | 260 | 159 |
Oregon | 389 | 303 | 277 | 322 | 1006 | 921 | 892 | 991 | 263 | 231 | 197 | 184 |