# Power Pivot Principles: Finding the Top N Values in a Dataset

21 April 2020

*Welcome back to the Power Pivot Principles blog. This week, we are going to learn a method of finding the top N value in a dataset.*

In business cases, one important metric in sales performance analysis is to understand which customer groups or customer orders have contributed most to the company. There are several DAX functions and methods could help us to determine the amount. The DAX function, **TOPN**, is one such example. This function returns the top **N** rows of the specified table. It uses the following syntax to operate

**TOPN (n_value, table, orderBy_expression, [order[, orderBy_expression, [order]]…]))**

where:

**n_value**represents the number of rows to return. It is any DAX expression that returns a single scalar value, where the expression is to be evaluated multiple times (for each row / context)**table**represents any DAX expression that returns a table of data from where to extract the top**N**rows**orderBy_expression**represents any DAX expression where the result value is used to sort the table, and it is evaluated for each row of table**order**indicates an optional value that specifies how to sort**orderBy_expression**values, ascending or descending.

Let’s look at a simple example. Suppose we have following two tables, the **Product **table and **Sales** table (the **Sales** table is not displayed in full):

They have the following relationship (one-to-many relationship based on the **ProductKey **field):

Next, suppose we want to obtain the top sales value (*i.e. *“top 1 value“) in the **Sales** table. We may write the DAX syntax as following:

**=CALCULATE(SUM(Sales[Sum of Sales]),TOPN(1,Sales,Sales[Sum of Sales],DESC))**

We calculate the sum of sales and apply the filter by using the **TOPN** function. In the **TOPN** function, we specify the top one (1) row to be returned in descending order. Then, we export the result as a PivotTable and choose the **ProductKey**, **Product Name** and **Top 1 Sales** as fields. The result would be:

In this scenario, we can see the top one (1) sale for each product. This result can be confirmed further by the raw data below:

We may implement another measure which calculates the top five (5) sales values in same way.

**=CALCULATE(SUM(Sales[Sum of Sales]),TOPN(5,Sales,Sales[Sum of Sales],DESC))**

The resulting PivotTable would be:

Let’s compare the result in the PivotTable with the raw data:

We can see that for product 1, the total of the top five (5) records is 351 (allowing for roundings), which is the same result shown in the PivotTable above. The measure ‘**Top 5 Sales**’ summarises the amount of the first five records in descending order.

Finally, we may write another measure which calculates the top three (3) sales values. This time, we want to create a chart to identify the trend for different top **N** measures.

**=CALCULATE(SUM(Sales[Sum of Sales]),TOPN(3,Sales,Sales[Sum of Sales],DESC))**

Then, we may create the following PivotChart:

We list the top **N **values according to different product names. Thus, we can see different growth rates for each product.

That’s it for this week!

Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.