How does the TOPN function (DAX) work?
The TOPN function (DAX) returns the top N rows of the specified table.
TOPN Formula Syntax
<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
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))