cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

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

1 ACCEPTED SOLUTION
Community Support

Hi @Krystyna ,

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

``````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.

8 REPLIES 8
Community Support

Hi @Krystyna ,

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

``````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.

Frequent Visitor

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

Regular Visitor

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?

Responsive Resident

@Krystyna
Hi,
Try This One

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

Thanks ,
Thennarasu

Regular Visitor

Results:

Many doubled ranks when value is completely different

Responsive Resident

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

Thanks,
thennarasu

Super User

@Krystyna , Try like

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

Regular Visitor

Still all are 1

Announcements

Fabric certifications survey

Certification feedback opportunity for the community.

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors