cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
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
Employee

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

So you may ignore that

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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

Note that your sample data only includes three countries.

Employee
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

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors