Measure to Show The Name of Top N Every Month

[angsoka]

Dear All,

I have a database of monthly sales for every region. I want to have a table showing only the top 3 of it.

Database

City	Jan	Feb	March
Boston	100	300	500
San Francisco	300	500	100
New York	200	200	400
Chicago	400	100	200
Los Angeles	500	400	300

 
What I want to have is a table showing list of monthly top 3 city 

Rank	Jan	Feb	March
1	Los Angeles	San Francisco	Boston
2	Chicago	Los Angeles	New York
3	San Francisco	Boston	Los Angeles

Any assistance?  Appreciate the help!

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

Top 3 city name measure: =
VAR _rankingnumber =
    MAX ( 'Rank number table'[Rank] )
VAR _rankingtable =
    FILTER (
        ADDCOLUMNS (
            VALUES ( City[City] ),
            "@Ranking", RANKX ( ALL ( City[City] ), CALCULATE ( SUM ( Data[Value] ) ),, DESC )
        ),
        [@Ranking] = _rankingnumber
            && [@Ranking] <= 3
    )
RETURN
    IF ( HASONEVALUE ( 'Month'[Month] ), MAXX ( _rankingtable, City[City] ) )

[angsoka]

Dear Mr. Jihwan_Kim What a beautiful solution. Many thanks. May I ask how many years you've spent until you reached this level? Best regards, Angsoka