Downloads
DAXRanking and windowsIntermediate

TOPN

Returns the top n rows of a table, ordered by an expression, highest first unless you say ASC.

Syntax
TOPN(<n_value>, <table>, <orderBy_expression> [, <order>] [, <orderBy_expression> [, <order>]] ...)

Returns: A table.

What it does

TOPN returns the first n rows of a table after sorting by the expression you give. The default order is highest first. Use it to build a top-customers list, or to total only the top products and show the rest as one "Others" line. Ties at the cutoff can return more than n rows.

Syntax and arguments

Example

The total sales of the five best-selling products among the products the user has selected, as a measure.

Top 5 Product Sales =
CALCULATE (
    [Total Sales],
    TOPN (
        5,
        ALLSELECTED ( Product[ProductName] ),
        [Total Sales],
        DESC
    )
)

ALLSELECTED makes the top five come from the products the user selected. CALCULATE then adds up their sales. An "Others" line can be the total sales minus this figure.

Example: a second use

A list of the three largest stores by sales, returned as a table in a DAX query.

EVALUATE
TOPN (
    3,
    ALL ( Stores[StoreName] ),
    [Total Sales],
    DESC
)

ALL ranks every store, ignoring the store filter, so the list is the company's top three. Used in a calculated table instead, the list would be fixed at refresh.

Things to watch

In business terms

Top customers for a receivables concentration report, best-selling products for a margin review, and an "Others" bar in a chart so the total still reconciles to the company figure.

Related functions

All DAX functions · View as Markdown · DAX fundamentals