RankX - To avoid ties - Rank over two calculate criteria

lager1001

New Member
Joined
May 17, 2019
Messages
37
I have my RankX formula working great but I do notice ties in some instances and want to avoid those. So I want to add to this rank order, another calculated criteria. Currently I am ranking Vendors by count of rows (count of transactions) but in some cases I get a tie. In order to break those ties I'd like to not only rank 1st by count of instances but additional by the sum of quantities shipped (separate column) by vendor for those counted rows. How can incorporate both calculations into one RankX formula?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,198
Office Version
2007
Platform
Windows
It's hard to imagine how your data is, but here I show you an example, I hope it helps you.

Dante Amor
ABCDEFG
1VENDORQTYVENDORTRANSACTIONSQTYRANK
2VENDOR21VENDOR2363
3VENDOR22VENDOR3372
4VENDOR23VENDOR42174
5VENDOR34VENDOR5165
6VENDOR32VENDOR64541
7VENDOR31
8VENDOR415
9VENDOR42
10VENDOR56
11VENDOR612
12VENDOR613
13VENDOR614
14VENDOR615
Hoja7
Cell Formulas
RangeFormula
E2:E6E2=COUNTIFS($A$2:$A$14,D2)
F2:F6F2=SUMIF($A$2:$A$14,D2,$B$2:$B$14)
G2:G6G2=COUNTIF($E$2:$E$6,">"&E2)+COUNTIFS($E$2:$E$6,E2,$F$2:$F$6,">"&F2)+1
 

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
581
Office Version
365, 2013
Platform
Windows
Can you share your current DAX formula and some exemplary data? Did you create RANKX as a new measure or as a new column?
 

lager1001

New Member
Joined
May 17, 2019
Messages
37
I created it as a new calculate column. My current DAX formula is as follows and works perfectly but again I get ties for any vendors with the same amount of rows (transactions) and I need to break this tie:

IF(AND(Data[Vendor Number]<>BLANK(),Data[Filter_12_Weeks]="within prev 12 weeks"),RANKX (ALLSELECTED ( Data[Vendor Number]),CALCULATE (COUNTROWS ( Data),ALLEXCEPT ( Data,Data[Vendor Number], Data[Filter_12_Weeks])),,DESC,Dense))

What I need is to somehow incorporate a sum of the [QTY] column for each [Vendor Number]. It should rank first by the count of rows per [Vendor Number] and per each[Filter_12_Weeks] as it does in the above formula, but secondly by the highest sum of [QTY] per each [Vendor Number] and [Filter_12_Weeks]. This should break ties and give me a true ranking. Make sense?
 

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
581
Office Version
365, 2013
Platform
Windows
Thanks for explaining.

Here's one idea for you to try - how about combining Count of rows with Quantity, and then ranking the whole dataset by this combined number?
What I mean is:
1585259224625.png


In DAX it should look somewhat like that:
Rich (BB code):
cRank = 
    var CR = CALCULATE(COUNTROWS(DataV), ALLEXCEPT(DataV, DataV[Vendor Number], DataV[Filter_12_Weeks]))
    var Q = CALCULATE(SUM(DataV[Quantities]))
    var NewMetric = FORMAT(CR & "," & Q, "0.00")
return
    NewMetric
You can then use this NewMetric in your RANKX formula, instead of CALCULATE (COUNTROWS ( Data),ALLEXCEPT ( Data,Data[Vendor Number], Data[Filter_12_Weeks])).
Let me know if this worked for you.
 

lager1001

New Member
Joined
May 17, 2019
Messages
37
Got this in. But it doesn't seem to be ranking correctly. Still experimenting.
 
Last edited:

lager1001

New Member
Joined
May 17, 2019
Messages
37
Yep, not doing what I need it to do. But I like the idea. I'm considering now to do three rankings: Rank 1 by Count, Rank 2 by QTY, Combine the two rankings per the metric, then rank the metric. Will follow up with my results.
 

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
581
Office Version
365, 2013
Platform
Windows
Fingers crossed! I know it's not really a straightforward method but I'm hoping this sneaky idea will work for you :)
 

Forum statistics

Threads
1,089,515
Messages
5,408,740
Members
403,224
Latest member
rholmesa

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top