Rankx for every year

[Krystyna]

Hello Krystyna here

I am new in PowerBi world and I need some help with a RANKX function.

My data has only three columns:

ProductId, Year, Value

I need to rank products for every year. I created such measure

ProductRank = RANKX(ALL('Table'[Year]), CALCULATE(SUM('Table'[Value]), ALLEXCEPT('Table','Table'[Year])))

 But the result is 1 for every ProductId.

What am I doing wrong?

Thanks

K

[v-jianboli-msft · Accepted solution]

Hi @Krystyna ,

Based on your description, I have created a simple sample:

Please try:

Measure =
RANKX(ALLEXCEPT('Table','Table'[Year]),CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Year]),[ID]=EARLIER('Table'[ID]))),SUM('Table'[Value]),,Dense)

Output:

Best Regards,

Jianbo Li

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

[Moehimby]

For me, it created a circular dependance on [YEAR] and failed.

[Krystyna]

Hi Jianbo LI and thx for your help.

It works but only with Year column from this table. I use other table as a time dimension (only one column with year integer) and when I use this dimension it does not work. Could you give me a hint how should I change this measure formula? Or maybe should I add it as a column in the table rather than measure?

Thx in advance

K 

[Thennarasu_R]

@Krystyna 
Hi,
Try This One 

ProductRank = RANKX(ALL('Table'[Year]), CALCULATE(SUM('Table'[Value]),,Desc)

Thanks ,
Thennarasu

[Krystyna]

Results:

Many doubled ranks when value is completely different

[Thennarasu_R]

@Krystyna 
Rewrite Dax  Inside the ALL Functions Change column  to  Table 

Thanks,
thennarasu 

[amitchandak]

@Krystyna , Try like

ProductRank = RANKX(filter(ALL('Table'[Year], Table[Product]), Table[Year] = Max(Table[Year])), CALCULATE(SUM('Table'[Value])),,desc,dense)

Measure Rank: https://www.youtube.com/watch?v=DZb_6j6WuZ0&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=40

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

[Krystyna]

Still all are 1