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 

  • Moehimby's avatar
    Moehimby
    Helper III
    2 years ago

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