
DeepSee MDX Reference
TOPPERCENT



Server:docs1 
Instance:LATEST 
User:UnknownUser 


Sorts a set and returns a subset from its highervalued
end, given a cutoff percentage that is applied to a total across set
elements.
TOPPERCENT(set_expression, percentage, ordering_expression)


percentage is a numeric
literal that is less than or equal to 100. That is, 15 represents
15 percent.
The function uses this argument to determine
the cutoff point for elements to return in the subset.
There is usually a member that straddles the cutoff point; this
member is assigned to the upper set, rather than the lower set. As
a result, in the returned subset, the cumulative total for
ordering_expression could be greater than
percentage, as a percentage of the entire set.

The function
evaluates this expression for each element of the set and sorts the
elements of the set in descending order according to this value. Any
hierarchies are ignored.
First consider the following query and the results it returns:
SELECT MEASURES.[%COUNT] ON 0,
TOPPERCENT(birthd.decade.MEMBERS, 100, MEASURES.[%COUNT]) ON 1 FROM patients
Patient Count
1 2000s 157
2 1980s 155
3 1990s 144
4 1960s 136
5 1970s 128
6 1950s 107
7 1930s 56
8 1940s 54
9 2010s 44
10 1920s 13
11 1910s 6
Because
percentage is 100, all members
are returned.
Now consider a variation of the preceding, in which
percentage is 50, so that we see the top 50 percent:
SELECT MEASURES.[%COUNT] ON 0, TOPPERCENT(birthd.decade.MEMBERS, 50, MEASURES.[%COUNT]) ON 1 FROM patients
Patient Count
1 2000s 157
2 1980s 155
3 1990s 144
4 1960s 136
The total for the
%COUNT measure for these
members is a little more than 50% of the total. (If the 1960s were
omitted, the total count would be less than 50%.)