Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm trying to rank the the following table:
But when i use the measurer:
Measure =
RANKX(
ALL(Consolidated[manufacturer_description]),
SUM(Consolidated[quantity])
)
It returns the rank 1 for all of my column Name.
Can somone help me with this?
I saw similar cases here, but none of the solutions worked.
Solved! Go to Solution.
Hi @E_,
It seems like you have encountered the classic RANKX returning 1 problem. You have written most of the formula correctly, the issue lies in the SUM(Consolidated[quantity]) part. You are trying to rank the sum of all the quantity from the Consolidated table. This will always return the same value (the total sum) for each row, thus every row gets the same rank of 1.
This SUM part needs to be enclosed within CALCULATE. It's all about row context.
The DAX formula should look like -
Measure =
RANKX(
ALL(Consolidated[manufacturer_description]),
CALCULATE(SUM(Consolidated[quantity]))
)
The above should work and give you the correct results. I would have explained here why this happens and how to use RANKX correctly, but there are very well-documented blogs that talk about this in great detail, especially RADACAD's 3-part series on RANKX. Please refer to the following links below -
Do let me know if you have any questions!
Now
Hey @E_ ,
try to go with number for month (not Text, like January). If you have to use month names you have to make sure not to sort your month name by month number in your date table.
Then it schould generally work.
I tried different approaches on that. I could reach correct results.
When I remove the sorting in my Date table it worked fine but you don`t have the proper sorting of month name.
Or you go with month number.
Regards
Hi @E_,
It seems like you have encountered the classic RANKX returning 1 problem. You have written most of the formula correctly, the issue lies in the SUM(Consolidated[quantity]) part. You are trying to rank the sum of all the quantity from the Consolidated table. This will always return the same value (the total sum) for each row, thus every row gets the same rank of 1.
This SUM part needs to be enclosed within CALCULATE. It's all about row context.
The DAX formula should look like -
Measure =
RANKX(
ALL(Consolidated[manufacturer_description]),
CALCULATE(SUM(Consolidated[quantity]))
)
The above should work and give you the correct results. I would have explained here why this happens and how to use RANKX correctly, but there are very well-documented blogs that talk about this in great detail, especially RADACAD's 3-part series on RANKX. Please refer to the following links below -
Do let me know if you have any questions!
It works.Thank you for the help and the links, it'll be helpful on future projects!!
I don't understand why do I have to use the
CALCULATE(SUM(Table[Column]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |