Need help with Rank function dax

[Ebrahim1123]

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. 

[Anonymous · Accepted solution]

Hi @Ebrahim1123 

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.

[Fowmy]

@Ebrahim1123 

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]

create a calculated column :

col = RANKX(FILTER(table,table[year]=EARLIER(table[year])),table[Runs])