Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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:
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
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:
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |