# Power BI: Using TOPN to Summarise the Top Two Countries in a Dataset

20 August 2020

*Welcome back to this week’s edition of the Power BI blog series. This week, Jonathan Liau looks at how to group the sales of the top two (say) countries in a dataset. *

Imagine that we have the following dataset loaded into Power BI:

Further, let’s say we have also created the following visualisations:

Having the total sales totalled by the entire year generates large numbers and arguably does not tell us so much about our data. Let’s imagine that we want to create a column that displays the sum of the top two countries in our dataset.

To calculate the total sum of sales of the top two countries, we can use the **TOPN** function. This allows us to take the top two or top 11 or top 937 or…

The **TOPN** function has the following syntax:

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

This function returns with the top **N** number of rows in the specified table. Furthermore, it should be noted that:

- the
**n_value**parameter defines the number of rows to return - the
**table**parameter identifies the table to be filtered by the**orderBy_expression** - the
**orderBy_expression**is the expression used to order the rows in the**table** - the
**order**parameter determines if the table should be organised either in ascending or descending order.

We can write the following measure to calculate the sum of the top 2 countries in our dataset:

**Sales of Top 2 Countries = CALCULATE([Total Sales],TOPN(2,values(Geography[Country]),[Total Sales],DESC))**

We may use the **TOPN** function as the filter expression in a **CALCULATE** function. To explain how we have coded the **TOPN** function:

- we define the first expression
**n_value**as two (2), as we want to summarise the top two countries - the second parameter requires a
**table**. Therefore, we used the**VALUES(Geography[Country])**, as the**VALUES**function will return with a table of just the values in the country field. This will allow the**TOPN**function to rank the**Country**field versus the entire**Geography**table - the third expression
**orderBy_expression**is the expression that we want to order the**table**by, in this case, we use the**[Total Sales]**measure because we want to rank the total sales - finally, we order the expression in descending order, hence we use
**DESC**.

The measure returns with the following results when plotted in the visualisations we created earlier in this blog:

Performing a sense check we create the following table:

From the table above, we may cross check that the countries with the top two sales in our dataset are Australia and the United States. Most importantly 1,309,047.20 + 1,100,549.45 = 2,409,596.65.

That’s it for this week, more on Power BI and the **TOPN** function in future blogs!

In the meantime, please remember we offer training in Power BI which you can find out more about here. If you wish to catch up on past articles, you can find all of our past Power BI blogs here.