TOPN
Returns the top n rows of a table, ordered by an expression, highest first unless you say ASC.
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
- n_value: the number of rows to keep.
- table: the table to rank, such as
VALUES(Product[ProductName]). - orderBy_expression: the value to sort by, usually a measure.
- order (optional):
DESC, the default, orASC.
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
- Ties at the cutoff can add rows, so the count can be more than n.
- Put
TOPNinsideCALCULATEwithALLSELECTEDwhen the set should follow the slicers. Otherwise the top n is decided by the outer filter, which may not be what the reader expects. - Measures inside
TOPNare evaluated for each row. On long lists this is slow. - To show a rank next to each row, use
RANKXinstead. - Read the label carefully. "Top 5" with a slicer on five products is not really a top five.
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.