Power BI Blog: Summarising the Top Two Countries in a Dataset

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:

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:

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.