Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

TOPN for Ranking the top 3 elements

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's avatar
    Anonymous
    3 years ago

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I tried using the Filters Panel as well: 

     

     

  • AilleryO's avatar
    AilleryO
    Memorable Member
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
    Anonymous
    Not applicable
    3 years ago

    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! 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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