Ranking unique values by giving same rank to all duplicates

[ksrini]

Hi,

I have to rank/index unique values in column which has year-week combination which goes like 2015-01, 2015-02..... 2015-52, 2016-01...to say 2020-30. Per each week, there are several entries, so i need to assign same rank irrespective of the number of duplicates. I tried RankEQ but i could not get it. Can you please suggest any ideas ? 

Thanks,

Srinivas

[v-stephen-msft · Accepted solution]

Hi @ksrini ,

You may try to use RANKX function. Since you have a year-week combination, there may be a column for the number of weeks and a column for the number of years. I will use these two columns for sorting. If not, it doesn't matter, you can follow the steps below.

1.I have a dataset which has the Date column and the Sales column. Create calculated columns as follows to get year-week combination.

WEEKNUMBER = WEEKNUM([Date],2)

YEAR = YEAR([Date])

YEARWKKE = [YEAR]&"-"&[WEEKNUMBER]

2.Now create other calculated columns to get ranking.

RANKYEAR = RANKX(ALL('Table'),[YEAR],,ASC,Dense)

RANKWEEK = RANKX(ALL('Table'),[WEEKNUMBER],,ASC,Dense)

RANKXYEAR&WEEK = RANKX ( ALL ( 'Table' ), [RANKWEEK] + [RANKYEAR] * 100,, ASC, DENSE )

You can check more details from here.

Best Regards,
Stephen Tao

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

[ksrini]

Brilliant solution @v-stephen-msft. Really appreciate for the details and the example. It worked perfectly across all duplicates. I did try Rankx earlier itself but we need to standardize the two fields Year, Weeknum to get correct rank. Thanks for your help.

Regards,

Srinivas

[pranit828]

HI @ksrini 

Check teh below link.

https://community.powerbi.com/t5/Community-Blog/Power-BI-Handling-Duplicate-Rankings-Using-the-RANKX...