Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
A123-Chauhan
Employee
Employee

Calculate the second and third highest countries by Sales values

Sample data attached 

 

We need to know the top ,top 2nd country and top 3rd country with highest sum of sales. Need to create 3 measures accordingly.

 

 

 

 

Country	Sales	Year 	Month
India	1000	2022	Jan
India	2000	2022	Feb
India	3000	2022	Mar
India	4000	2022	Apr
India	5000	2022	May
India	6000	2022	Jun
India	7000	2022	Jul
India	8000	2022	Aug
India	9000	2022	Sep
India	10000	2022	Oct
India	11000	2022	Nov
India	12000	2022	Dec
India	13000	2023	Jan
India	14000	2023	Feb
India	15000	2023	Mar
India	16000	2023	Apr
India	17000	2023	May
India	18000	2023	Jun
India	19000	2023	Jul
India	20000	2023	Aug
India	21000	2023	Sep
India	22000	2023	Oct
India	23000	2023	Nov
India	24000	2023	Dec
Russia	1000	2022	Jan
Russia	6000	2022	Feb
Russia	11000	2022	Mar
Russia	16000	2022	Apr
Russia	21000	2022	May
Russia	26000	2022	Jun
Russia	31000	2022	Jul
Russia	36000	2022	Aug
Russia	41000	2022	Sep
Russia	46000	2022	Oct
Russia	51000	2022	Nov
Russia	56000	2022	Dec
Russia	61000	2023	Jan
Russia	66000	2023	Feb
Russia	71000	2023	Mar
Russia	76000	2023	Apr
Russia	81000	2023	May
Russia	86000	2023	Jun
Russia	91000	2023	Jul
Russia	96000	2023	Aug
Russia	101000	2023	Sep
Russia	106000	2023	Oct
Russia	111000	2023	Nov
Russia	116000	2023	Dec
China	200	2022	Jan
China	400	2022	Feb
China	600	2022	Mar
China	800	2022	Apr
China	1000	2022	May
China	1200	2022	Jun
China	1400	2022	Jul
China	1600	2022	Aug
China	1800	2022	Sep
China	2000	2022	Oct
China	2200	2022	Nov
China	2400	2022	Dec
China	2600	2023	Jan
China	2800	2023	Feb
China	3000	2023	Mar
China	3200	2023	Apr
China	3400	2023	May
China	3600	2023	Jun
China	3800	2023	Jul
China	4000	2023	Aug
China	4200	2023	Sep
China	4400	2023	Oct
China	4600	2023	Nov
China	4800	2023	Dec

 

 

 

 

 

 

 

 

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

This can be done with a single measure.  Share data of more than 3 countries.  Also, please clarify what the result should be if there is a tie in sales.  


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

You may add for one dummy country if you want. In my case, tie situation will not be there. 

So you may ignore that

DIY.  Read this article - Vendor Analysis In Inventory Management Dashboards (enterprisedna.co)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

OK. What have you tried and where are you stuck?

 

Note that your sample data only includes three countries.

FIRSTNONBLANK(TOPN(2 , VALUES('Table'[Country]),sum('Table'[Sales]) ),1) but it is not giving desired results.
 
I have some date slicers in the page

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors