TOPN for Ranking the top 3 elements

[Anonymous]

Hi, 

I've been using TOPN to create a Ranking that looks like this: 

The measures I used are: 

For Country Name: 

_1st Place =

MAXX (

    TOPN (

        1,

        SUMMARIZE (

            'GSW Active',

            'GSW Active'[Country],

            "Sum", [_Total Points]

        ),

        [Sum], DESC

    ),

    [Country]

)

For Country Flag: 

_1st Place Flag =

MAXX (

    TOPN (

        1,

        SUMMARIZE (

            'GSW Active',

            'GSW Active'[Wave Flags],

            "Sum", [_Total Points]

        ),

        [Sum], DESC

    ),

    [Wave Flags]

)

Everything works wonders for 2022 as you see above... but when I use the slicer to filter a different year/month. It's all going crazy. 


Do I need to add anything to these measures? 

The table is: 
Country/Month/Points/FlagURL

[_Total Points] = SUM of [Points]
Month is a 1st of each month - year date. 
In the slicer I use the same field and the yierarchy for year and month. 

Any ideas what I'm doing wrong? 

Thank you!

[Anonymous · Accepted solution]

Found a measure that works in this thread: https://community.powerbi.com/t5/Desktop/Top-Salesperson-displayed-in-a-card/m-p/598439 

Need to see if I can add another criteria of ranking to make it error proof (in case there are equal points in the first places). Please let me know if I can add anything to make this happen. 

_Top country =

VAR temp_table =

    SUMMARIZE (

        ALL ( 'GSW Active'[Country] ),

        'GSW Active'[Country],

        "SalesTotalValue", SUM ( 'GSW Active'[Total Points] )

    )

RETURN

    CALCULATE (

        MAX ( 'GSW Active'[Country] ),

        FILTER (

            ADDCOLUMNS ( temp_table, "Rank", RANKX ( temp_table, [SalesTotalValue] ) ),

            [Rank] = 1

        )

    )

[Anonymous]

Tried to simplify things: 

Made a new page, where I added the leaderboard again and the top 3 countries and I have the same result, no matter what I try... works well for 2022 for some reason (maybe coincidence?) and then it goes wrong for 2021 and 2023. 

Filter 2022: 

Filter 2021:

I'm totally out of ideas... never thought that ranking and displaying a top value can be such a hard thing in a Business Intelligence tool... 

Please help! 

[AilleryO]

Hi,

Can you give us more information about your model (tables and relations). 

For example to help you we need to know where the year you are using to filter is coming from ?

Which table holds it ? And what are the relationship between this table and the others ?

Let us know so we might be able to help, but It do think it comes from a relationship problem.

[Anonymous]

hi @AilleryO

They are all in the same table: 

_Total Points = CALCULATE(SUM([Total Points]))

Country / Month / Total Points / Flag URL 

The slicers are the "Month" Column

Please let me know your thoughts. Thank you!

[Anonymous]

I tried using the Filters Panel as well: