topic Overall weighted RANK of multiple RANKs in DAX Commands and Tips

Overall weighted RANK of multiple RANKs

roshenrrujj — Tue, 25 Jul 2023 23:44:32 GMT

hey guys, could you PLEASE help me with this DAX query below?

i have a list of contractors with multiple ranks and i need to produce the overall rank with weightings. However when i try multipling each RANK with its corresponding weighting, it gives top ranks to the contractors with a lot of blank ranks.

Rank	%Weighting
Rank 1	0.2
Rank 2	0.2
Rank 3	0.15
Rank 4	0.1
Rank 5	0.3
Rank 6	0.05

ContractorID	Rank 1	Rank 2	Rank 3	Rank 4	Rank 5	Rank 6
CON111	20	48	7	14	22	48
CON112	117	21	5	8	3	22
CON113	62	39	1	1
CON114	2	99	5	6	22	4
CON115	82	142	32	43	42	42
CON116	118	143	34	3	42
CON117	115	93	28	38	42	22
CON118	106	116	29	34	42	1
CON119	105	109	25	37	12	33
CON120	89	150	1	32	42
CON121	101	108	17	21	27	34
CON122	91	138	11	10	10	45
CON123	100	106	7	11	33	44
CON124	111	74	15	8	42	45
CON125	71	105	20	24	41	30
CON126	35	110	13	32	42	54
CON127	107	96	8	12	42	18
CON128	73	97	23	29	42	13
CON129	49	137	21	25	42
CON130	77	81	20	28	42	26
CON131	59	121	18	27	23	25
CON132	88	72	7	12	42	49
CON133	80	95	17	21	36	16
CON134	58	131	22	23	22	7
CON135	67	79	24	30	11	51
CON136	55	146		14	42
CON137	65	103	5	19	7	53
CON138	53	114	17	25	32	10
CON139	120	130
CON140	13	134	5		42	56
CON141	54	89	14	12	39	37
CON142	108	136
CON143	10
CON144	8
CON145		7
CON146			6			1
CON147		5
CON148			5
CON149	5
CON150	4
CON151		4
CON152			4
CON153		3
CON154	3
CON155		2
CON156						1
CON157						1
CON158
CON159
CON160
CON161
CON162
CON163
CON164
CON165
CON166
CON167

i produced each Rank with this DAX:

IF( ISBLANK(measure),
BLANK(),
RANKX(
filter(
ALL(DATA[ContractorID]),
measure>0),
measure,,
ASC,
Dense))

if this helps:
I have another report, in which i know that CON113, CON112 and CON111 are one of the top ranking contractors.

Re: Overall weighted RANK of multiple RANKs

technolog — Sun, 03 Sep 2023 17:49:14 GMT

The problem you're facing is that when you're multiplying each rank by its weighting, contractors with many blank ranks are getting top ranks. This is because blank or missing ranks are being treated as 0, and when multiplied by the weighting, they don't affect the overall score. As a result, contractors with many blank ranks get an advantage.

To solve this, you need to calculate a weighted score for each contractor and then rank them based on this score. Here's how you can do it:

First, you need to calculate the weighted score for each contractor. For each rank column, you'll multiply the rank by its weighting. Then, sum up all these weighted scores for each contractor to get the overall score.

For example, for CON111, the weighted score would be:
(20 * 0.2) + (48 * 0.2) + (7 * 0.15) + (14 * 0.1) + (22 * 0.3) + (48 * 0.05)

Once you have the weighted score for each contractor, you can then rank them based on this score. The contractor with the lowest score (since lower ranks are better) will be ranked 1, the next lowest will be ranked 2, and so on.

Here's a DAX formula to calculate the weighted score for each contractor:

WeightedScore =
DATA[Rank 1] * 0.2 +
DATA[Rank 2] * 0.2 +
DATA[Rank 3] * 0.15 +
DATA[Rank 4] * 0.1 +
DATA[Rank 5] * 0.3 +
DATA[Rank 6] * 0.05
Now, you can rank the contractors based on this weighted score:

css
Copy code
WeightedRank =
RANKX(
ALL(DATA[ContractorID]),
[WeightedScore],
,
ASC,
Dense
)
This will give you a rank based on the weighted score, with the contractor having the lowest score ranked 1, and so on. This should solve your problem and give you a more accurate ranking of the contractors.