Share via


TopPercent

The TopPercent function returns, in order of decreasing rank, the top-most rows of a table whose cumulative total is at least a specified percentage.

Syntax

TopPercent(<table expression>, <rank expression>, <percent>)

Applies To

An expression that returns a table, such as a <table column reference>, or a function that returns a table.

Return Type

<table expression>

Remarks

The TopPercent function returns the top-most rows in decreasing order of rank based on the evaluated value of the <rank expression> argument for each row, such that the sum of the <rank expression> values is at least the given percentage that is specified by the <percent> argument. TopPercent returns the smallest number of elements possible while still meeting the specified percent value.

Examples

For example, assume that a table column named Products contains the following data:

Product name Unit sales

Apples

30

Kiwi fruit

10

Oranges

40

Lemons

20

In this example, the following function:

TopPercent([Products], [Unit Sales], 60) 

returns the following table:

Product name Unit sales

Oranges

40

Apples

30

Note that Apples is selected instead of Lemons.

See Also

Reference

Data Mining Extensions (DMX) Function Reference
Functions (DMX)
Mapping Functions to Query Types (DMX)

Help and Information

Getting SQL Server 2005 Assistance