Forum Discussion
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
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.
8 Replies
v-jianboli-msftCommunity Support
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.
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
MoehimbyHelper III
For me, it created a circular dependance on [YEAR] and failed.
- amitchandak
Super User
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-measuresStill all are 1
- Thennarasu_R
Responsive Resident
Krystyna
Hi,
Try This OneProductRank = RANKX(ALL('Table'[Year]), CALCULATE(SUM('Table'[Value]),,Desc)
Thanks ,
ThennarasuResults:
Many doubled ranks when value is completely different
- Thennarasu_R
Krystyna
Rewrite Dax Inside the ALL Functions Change column to Table
Thanks,
thennarasu
