Top N ranking across a number of rows - is it possible?

[DeeMC]

Hi, I'm hoping someone can help.  I have a dataset which has passengers by country split by nationality. It looks like this with dep country as the rows, nationality as the colums and passengers as the values.  I'm trying to use Top N to work out the Top 10 nationalities for each country but I can't seem to get it right.  When I try filtering by Top 10 on nationality using passengers as the value it just returns the Top 10 nationalities for the whole dataset.  Any ideas? Is it possible to create one table that shows the Top 10 nationalities ranked by passengers for each country? 

[smpa01 · Accepted solution]

Hi @DeeMC 

Firstly, please refer to https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071... while posting as it is easier for the reponders to sove.

Howver, I recreated this using a small sample to show top3.

The PBIX is attached.

[themistoklis]

@DeeMC 

You cannot actually do that.

Just imagine that the matrix shows values across all nationalities and not for specific ones per row.

In other words if each country has a different list of top 10 nationalities then you will end up having way more columns than 10.

best way to get the result you want is to show both countries and nationalities as rows and finally show top 10 per country. It will be a straight table not matrix