cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## RANX - Create a sort column from a column that starts wih diffeent numbers

Hi all,

I have 12 weeks of data for every year. Some year's week starts with 24, someone starts 25 and some others start at 23, it changes year by year.
I realized I have always 12  weeks  and  week numbers are  not in order  and they start with different numbers.

I just want to create a new column with a condition like this.

no matter which number starts that year. the column will always start at 1 and end at 12

But I have a trouble  with your formule  When I add more product  to the data  Count RANX number is increasing.
is there a soulution for  this ??

``https://www.dropbox.com/sh/rrcmf97oupdb3fr/AAAEW6-mDPVOjc6k5WBKS90ba?dl=0``

His  formula

``Calculated Column = VAR RowYear = 'Table'[Year] VAR Result =    RANKX (        FILTER (            'Table',            'Table'[Year] = RowYear        ),        'Table'[Number],        ,        ASC    ) RETURN    Result``

My formula

``Column =  var minnumber= CALCULATE(MIN('table'[Number]),ALLEXCEPT(table,table[year])) return IF('table'[Number] = minnumber,1,'table'[Number]-minnumber+1)``

here is my table

 Year Product Number Amount Quene 2018 A 23 10 1 2018 A 24 12 2 2018 A 25 14 3 2018 A 26 20 4 2018 A 27 5 5 2018 A 28 3 6 2018 A 29 6 7 2018 A 30 30 8 2018 A 31 101 9 2018 A 32 17 10 2018 A 33 25 11 2018 A 35 65 12 2019 A 24 41 1 2019 A 25 22 2 2019 A 26 30 3 2019 A 27 3 4 2019 A 28 6 5 2019 A 29 5 6 2019 A 30 9 7 2019 A 31 66 8 2019 A 32 45 9 2019 A 33 18 10 2019 A 34 61 11 2019 A 36 57 12 2018 B 23 10 1 2018 B 24 12 2 2018 B 25 14 3 2018 B 26 20 4 2018 B 27 5 5 2018 B 28 3 6 2018 B 29 6 7 2018 B 30 30 8 2018 B 31 101 9 2018 B 32 17 10 2018 B 33 25 11 2018 B 35 65 12 2019 B 24 41 1 2019 B 25 22 2 2019 B 26 30 3 2019 B 27 3 4 2019 B 28 6 5 2019 B 29 5 6 2019 B 30 9 7 2019 B 31 66 8 2019 B 32 45 9 2019 B 33 18 10 2019 B 34 61 11 2019 B 36 57 12

1 ACCEPTED SOLUTION
Solution Sage

Sorry, I didn't realise when answering your original question that the same year and week could appear multiple times.

RANKX has an optional 5th argument which allows you to determine how ties should be handled.  When set to 'DENSE', all elements in a tie are counted only once. As such, my original solution can be modified as follows to give you the result you're looking for:

``````Calculated Column =
VAR RowYear = 'Table'[Year]
VAR Result =
RANKX (
FILTER (
'Table',
'Table'[Year] = RowYear
),
'Table'[Number],
,
ASC,
DENSE
)
RETURN
Result``````

When dealing with multiple row contexts, I find it much easier to use variables rather than the EARLIER function (as suggested by @Anonymous ) but that's entirely personal preference.

4 REPLIES 4
Solution Sage

Sorry, I didn't realise when answering your original question that the same year and week could appear multiple times.

RANKX has an optional 5th argument which allows you to determine how ties should be handled.  When set to 'DENSE', all elements in a tie are counted only once. As such, my original solution can be modified as follows to give you the result you're looking for:

``````Calculated Column =
VAR RowYear = 'Table'[Year]
VAR Result =
RANKX (
FILTER (
'Table',
'Table'[Year] = RowYear
),
'Table'[Number],
,
ASC,
DENSE
)
RETURN
Result``````

When dealing with multiple row contexts, I find it much easier to use variables rather than the EARLIER function (as suggested by @Anonymous ) but that's entirely personal preference.

Resolver I

hi @MartynRamsden ,
Thank you for your  quick reply. You are the best . If you  want something from istanbul /Turkey. Just tell me
May the force be with you

Anonymous
Not applicable
``Column = RANKX(FILTER('Table','Table'[Year]=EARLIER('Table'[Year])&& 'Table'[Product ]=EARLIER('Table'[Product ])),'Table'[Number ],,ASC)``

Please try this calculated column to get year & product wise ranking

Resolver I

Hi @Anonymous

Best regards.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.