Forum Discussion
How to sum first and RANKX after the group by ?
Hello,
I have a problem with RANKX(). It doesn't works as expected,I found the problem but I didn't find the solution...
Here an introduction the problem :
I have this table :
I want to display it by grouping values by OP and then sum the QTY.
So under the "values", I right clicked on my QTY and then choose "Sum" like this :
And here is the result :
So now, I tried to add a ranking by using RANKX(), so I added a new column "RANK" with this value :
RANK = RANKX(all(OP_QTY), OP_QTY[QTY])
Here is my result :
This isn't what I expected... the OP "a" should be ranked 1 and the OP "b" should be ranked 2. But here both a ranked 3.
So I tried to find why... and it's because of the grouping by when I sum, the RANKX() ranks all the values first without grouping. If I choose "don't summarize" for the QTY, you can see what he does :
And so, when I sum the qty, the table group by OP... so he sums the RANK to... that is why I have Rank 3 for OP "a" (since its 1+2).
So my question is : Is it possible to rank AFTER the sum ? I want this result :
With this database :
Thank you in advance.
Yes, you can do it my creating a couple of measures as below
Sum of Qty = SUM('Table'[Qty]) Ranking = var currentQty = [Sum of Qty] return RANKX( ALL('Table'[OP]), [Sum of Qty], currentQty)