Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Need help with Rank function dax

I have 3 Columns:- Year, Runs, and Batsman.  I need to find a batsman who has scored the most runs year-wise.
Like for
2016- 973 - Kohli 

2017 - 641 -Warner
2018 - 735 -Williamson and so.
I know we can achieve this through the Rank function but not able to figure out how as I am new to Power BI Dax. 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    For questions where you want to find the batsman who scores the most points per year via the rank function, we recommend that you use “rankx”, as “rankx” sorts the same values in the column to the same level. This avoids over-sieving or missing data.

     

    we have the following solutions:

     

    Here's some dummy data

     

    Create a measure, after grouping "Year", sort "Runs" in descending order

     

    Top_Rank_Batsman = RANKX(FILTER(ALL('Table'),[Year]=MAX('Table'[Year])),CALCULATE(SUM('Table'[Runs])),,DESC,Dense)

     

     

    Filter the data in "Filters" and select the record with a ranking of "1".

     

    Here is the result

     

    Best Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    Anonymous 

    If you need to show the top batsman by year on a visual, a masure will be useful as well. Add the Year colum to a table visual and add the following meaure:

    Rank Top = 
MAXX(
    TOPN( 1  , VALUES( Rankbyyear[batsman] ) , CALCULATE(SUM( Rankbyyear[runs] )) , DESC ),
    [batsman] 
)

     

  • Nithinr's avatar
    Nithinr
    Resolver III
    2 years ago

    create a calculated column :

    col = RANKX(FILTER(table,table[year]=EARLIER(table[year])),table[Runs])
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    For questions where you want to find the batsman who scores the most points per year via the rank function, we recommend that you use “rankx”, as “rankx” sorts the same values in the column to the same level. This avoids over-sieving or missing data.

     

    we have the following solutions:

     

    Here's some dummy data

     

    Create a measure, after grouping "Year", sort "Runs" in descending order

     

    Top_Rank_Batsman = RANKX(FILTER(ALL('Table'),[Year]=MAX('Table'[Year])),CALCULATE(SUM('Table'[Runs])),,DESC,Dense)

     

     

    Filter the data in "Filters" and select the record with a ranking of "1".

     

    Here is the result

     

    Best Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.