Forum Discussion

Krystyna's avatar
Krystyna
Regular Visitor
3 years ago
Solved

Rankx for every year

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 ...
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

     

     

     

v-jianboli-msft's avatar
v-jianboli-msft
Community Support
3 years ago

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.

 

 

 

Krystyna's avatar
Krystyna
Regular Visitor
3 years ago

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