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

[sinanalmac]

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

@MartynRamsden  helped me about this issue

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

 here is power BI  link

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

[MartynRamsden · Accepted solution]

Hi @sinanalmac 

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.

[sinanalmac]

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]

@sinanalmac 

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 

[sinanalmac]

Hi @Anonymous 
thank you for  Reply  I will try  your solution.

Best regards.