How to sum first and RANKX after the group by ?

[BlueCat]

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.

[johnt75 · Accepted solution]

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)

[BlueCat]

Thank you 🙂