# RankX - To avoid ties - Rank over two calculate criteria

#### lager1001

##### New Member
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?

### 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
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

#### lager1001

##### New Member
Thanks for the response. But I am looking to do this in DAX using the RANKX function. Any ideas?

#### JustynaMK

##### Well-known Member
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
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
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:

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
Got this in. But it doesn't seem to be ranking correctly. Still experimenting.

Last edited:

#### lager1001

##### New Member
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
Fingers crossed! I know it's not really a straightforward method but I'm hoping this sneaky idea will work for you

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