TOPN Function (DAX)

How does the TOPN function (DAX) work?

The TOPN function (DAX) returns the top N rows of the specified table.

TOPN Formula Syntax

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

How do you use the TOPN function?

This function returns a table that contains the top N items (N is the number you specified in the first argument) from the original data table according to the ranking attribute you specified. You can used this function to see the top performing products by sales, region or shop. This useful you need to wrap it in another function, such as SUMX or AVERAGEX. Also if you to calculate the Total Revenue of our Top 10 Sales each year.

Related Blog Posts

Related Support Forum Posts

Issue with Rankx TopN in Matrix
Ranking functions: TOPN
When & How to Use TOPN in Power BI – DAX Function Tutorial

Considerations when using the TOPN function

  • If there is a tie, in order_by values, at the N-th row of the table, then all tied rows are returned. Then, when there are ties at the N-th row the function might return more than n rows.
  • If the n_value is 0 (zero) or less then TOPN returns an empty table.
  • The TOPN does not guarantee any sort order for the results.

Related Video Tutorials

Formula examples using the TOPN function

=SUMX(TOPN(10, SUMMARIZE(Product, [ProductKey], “TotalSales”, SUMX(RELATED(InternetSales_USD[SalesAmount_USD]), InternetSales_USD[SalesAmount_USD]) + SUMX(RELATED(ResellerSales_USD[SalesAmount_USD]), ResellerSales_USD[SalesAmount_USD]))

= SUMX (TOPN (15,Sales,Sales[Salesperson],ASC),Sales[Sales Amount])

= CALCULATE([Total Revenue],TOPN([N Selected],’Sales Records’,’Sales Records'[Total Revenue],ASC))

Related Course Modules

Ultimate-Beginners-Guide-To-PowerBI
Center of Excellence

DOWNLOAD


Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.


Download DAX Formulas Reference Guide
Download DAX Formula Reference Guide
Membership