Overall weighted RANK of multiple RANKs

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.