TOP 10 across multiple tables?

[powerbirino2]

Hi.

I want to show top 5 countries by sales but I don't how I can do this. This is my database design.

 I currently have achieved a matrix that shows all country-sales like this:

COUNTRY	SALES
Spain	4.000.000
France	3.500.000
etc.	etc.

So, how could I show only top 5 countries by their sales?

I worked on another project where I used this function:

RANKX(
           ALLSELECTED(Table[Column]);
           [SumOfSmth];;
           0;
           Dense
)

but it was "to do a top" on one table, not across many tables

Thank you!

[v-micsh-msft · Accepted solution]

Hi powerbirino2,

I agree to most of the points posted by ankitpatira, but I don’t think adjust the Visual could make it to only show 5 tops. What I did to achieve that is through the following ways:

1. Add a visual level filter to filter the top 5 countries, based on the sales;
2. Create a new table which only returns the top 5 country;

To have both ways work, we need to configure the relationship of the three table, to have Cross filter direction set to both.

For adding Visual Level Filter, see the image, Under Power BI Desktop Report view, when Visual selected, choose the country name and the sales as selected column, then under Filters, there would be country and sales available to configure, the following is an example I created with some testing data:

Under the same testing data, to create a new table with only the top 5 country, under Power BI desktop, select Modeling->New table, and enter the following formula:

Top5 = topn(
      5,
      SUMMARIZE(
           sheet3, 
           Sheet3[Country], 
           "sales", 
           sumx(sheet1, Sheet1[Sales])
        ),
     [sales],
     DESC)

See:

Let me know if those would work for you.

If any further help needed, please feel free to post back.

Regards

[ankitpatira]

@powerbirino2 if you have the relationship established in power bi desktop as shown in picture then you don't need to use any measures. Simply use any visual for example bar chart and use country name and sales columns and it will automatically show you sales by countries. To get top 5, if you've used bar chart then on top right hand corner click ellipses (three dots) and use Sort By option to sort by sales. This will show you top sales and by countries and you can adjust the size of visual so that only top 5 countries are visible.