Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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
Solved! Go to 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.
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
@Krystyna
Hi,
Try This One
ProductRank = RANKX(ALL('Table'[Year]), CALCULATE(SUM('Table'[Value]),,Desc)
Thanks ,
Thennarasu
Results:
Many doubled ranks when value is completely different
@Krystyna
Rewrite Dax Inside the ALL Functions Change column to Table
Thanks,
thennarasu
@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
Still all are 1
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.