Forum Discussion

BlueCat's avatar
BlueCat
Frequent Visitor
4 years ago
Solved

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)

2 Replies

  • 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)