RankX - To avoid ties - Rank over two calculate criteria

[lager1001]

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?

 

[DanteAmor]

It's hard to imagine how your data is, but here I show you an example, I hope it helps you.

Dante Amor
	A	B	C	D	E	F	G
1	VENDOR	QTY		VENDOR	TRANSACTIONS	QTY	RANK
2	VENDOR2	1		VENDOR2	3	6	3
3	VENDOR2	2		VENDOR3	3	7	2
4	VENDOR2	3		VENDOR4	2	17	4
5	VENDOR3	4		VENDOR5	1	6	5
6	VENDOR3	2		VENDOR6	4	54	1
7	VENDOR3	1
8	VENDOR4	15
9	VENDOR4	2
10	VENDOR5	6
11	VENDOR6	12
12	VENDOR6	13
13	VENDOR6	14
14	VENDOR6	15
Hoja7

Cell Formulas
Range	Formula
E2:E6	E2	=COUNTIFS($A$2:$A$14,D2)
F2:F6	F2	=SUMIF($A$2:$A$14,D2,$B$2:$B$14)
G2:G6	G2	=COUNTIF($E$2:$E$6,">"&E2)+COUNTIFS($E$2:$E$6,E2,$F$2:$F$6,">"&F2)+1

 

[lager1001]

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

 

[JustynaMK]

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]

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]

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:

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]

Got this in. But it doesn't seem to be ranking correctly. Still experimenting.

 

[lager1001]

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]

Fingers crossed! I know it's not really a straightforward method but I'm hoping this sneaky idea will work for you

 

[lager1001]

I couldn't get this to work as I wanted it to. I know it has to be possible but I haven't discovered the proper syntax. I ended up doing it in Power Query as a double group by table and added an index column for my rank and ran with that. It works. Not what I really wanted but it works until I can figure out the power pivot DAX way of doing it.