Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I want to show the sales of TOP 7 countries for the selected year and the remaining sales I need to add up the country and show it as "Other" in Country Name column. After that, Based on current top 7 countries names i need to compare with previous last two years same country sales amount.
Calendar | Country Name | Total |
2024 | Qatar | 18.98 |
2024 | Australia | 15.28 |
2024 | Malaysia | 12.2 |
2024 | UK | 9.64 |
2024 | United States | 4.56 |
2024 | Netherlands | 3.32 |
2024 | Canada | 1.28 |
2024 | Others | 17.4 |
Expected Ouput: Comparing current top 7 countries with previous year
Calendar | Country Name | Total |
2024 | Qatar | 18.98 |
2024 | Australia | 15.28 |
2024 | Malaysia | 12.2 |
2024 | UK | 9.64 |
2024 | United States | 4.56 |
2024 | Netherlands | 3.32 |
2024 | Canada | 1.28 |
2024 | Others | 17.4 |
2023 | Qatar | 20.98 |
2023 | Australia | 17.28 |
2023 | Malaysia | 16.2 |
2023 | UK | 10.64 |
2023 | United States | 9.56 |
2023 | Netherlands | 8.32 |
2023 | Canada | 6.28 |
2023 | Others | 19.4 |
2022 | Qatar | 22.98 |
2022 | Australia | 17.98 |
2022 | Malaysia | 13.2 |
2022 | UK | 11.64 |
2022 | United States | 10.56 |
2022 | Netherlands | 9.32 |
2022 | Canada | 7.28 |
2022 | Others | 19.76 |
This measure i used to calculate top 7 and others, and also to display last 2 year sales value comparsion with current year sales values of the top 7 countries
Top7=
VAR Top_N = 7
VAR TOPNCOUNTRY = TOPN(Top_N,
ALL('COUNTRY'),
[Total Sales]
)
VAR ALLCOUNTRY = CALCULATE( [Total Sales],ALLSELECTED('COUNTRY'))
VAR OTHERCOUNTRY = ALLCOUNTRY - CALCULATE( [Total Sales,TOPNCOUNTRY)
VAR TOPNCOUNTRYDIS = CALCULATE( [Total Sales],KEEPFILTERS(TOPNCOUNTRY))
VAR CURRENTPCOUNTRY = SELECTEDVALUE('INTENSITY COUNTRY'[COUNTRY])
RETURN
IF( CURRENTPCOUNTRY = "Others",
OTHERCOUNTRY,
TOPNCOUNTRYDIS
)
Problem:
Previous year values for current top 7 countries name is not happening instead each year it displays top 7 and others
As long as you are fully aware that the Top 7 of the current year may not even exist in the prior year (which makes the comparison questionable) this is a straight forward process.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
User | Count |
---|---|
53 | |
46 | |
19 | |
16 | |
15 |
User | Count |
---|---|
114 | |
44 | |
44 | |
28 | |
22 |