Weighted Measure - Different Criterias

[bolwahnn]

Hello,
I'm looking for help on creating a measure that give me an overall score based off of other score Metrics.

I have 2 tables:
First table provides the employee scores for the metrics being measured:
[TABLE="width: 744"]
<tbody>[TR]
[TD]dDate[/TD]
[TD]RankGroup[/TD]
[TD]Full Name[/TD]
[TD]Production%[/TD]
[TD]Quality%[/TD]
[TD]Adherence%[/TD]
[/TR]
[TR]
[TD]Friday, June 28, 2019[/TD]
[TD]Baltimore[/TD]
[TD]Mickey Mouse[/TD]
[TD]106%[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday, June 27, 2019[/TD]
[TD]Baltimore[/TD]
[TD]Mickey Mouse[/TD]
[TD]111%[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday, July 1, 2019[/TD]
[TD]Baltimore[/TD]
[TD]Mickey Mouse[/TD]
[TD]110%[/TD]
[TD]100%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday, July 1, 2019[/TD]
[TD]Mesa[/TD]
[TD]Donald Duck[/TD]
[TD]97%[/TD]
[TD]100%[/TD]
[TD]94%[/TD]
[/TR]
[TR]
[TD]Thursday, June 27, 2019[/TD]
[TD]Mesa[/TD]
[TD]Donald Duck[/TD]
[TD]130%[/TD]
[TD]100%[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]Friday, June 28, 2019[/TD]
[TD]Mesa[/TD]
[TD]Donald Duck[/TD]
[TD]72%[/TD]
[TD]100%[/TD]
[TD]94%[/TD]
[/TR]
</tbody>[/TABLE]

Second Table provides the % weight for each of the metrics being measured above to calculate an overall score: (notice how Baltimore doesnt have and Adherence % above - They also dont Adherence in the table below because they are not measured on it).
[TABLE="width: 539"]
<tbody>[TR]
[TD]RankGroup[/TD]
[TD]Metric[/TD]
[TD]Scoring Weight[/TD]
[TD]Old/Current Metric[/TD]
[/TR]
[TR]
[TD]Baltimore[/TD]
[TD]Quality[/TD]
[TD]60%[/TD]
[TD]Current[/TD]
[/TR]
[TR]
[TD]Baltimore[/TD]
[TD]POP[/TD]
[TD]40%[/TD]
[TD]Current[/TD]
[/TR]
[TR]
[TD]Mesa[/TD]
[TD]Schedule Adherence[/TD]
[TD]10%[/TD]
[TD]Current[/TD]
[/TR]
[TR]
[TD]Mesa[/TD]
[TD]Quality[/TD]
[TD]50%[/TD]
[TD]Current[/TD]
[/TR]
[TR]
[TD]Mesa[/TD]
[TD]POP[/TD]
[TD]40%[/TD]
[TD]Current[/TD]
[/TR]
</tbody>[/TABLE]

I'm looking for help on how to create a measure that will caculate the Overall % based on the 2 tables above so that its using the correct Scoring Weight%. There are many more offices with different variations of the %'s so I'd need this to be able to look at the RankGroup to determine the correct % to be used.

Appreciate the help!

 

[wornhall]

That appears to be a problem that fits the exact profile of Excel's "SOLVER" feature. You will find all you need to know in the help library.

Hope this does the trick for you.

 

[bolwahnn]

Hi wornhall,
Can you elaborate? I'm not sure how excel solver can help me here.

I'm looking to create a Power BI measure utilizing the DAX language to calculate the Overall Score % for the 2 or 3 metrics (depending on the rankgroup).

 

[peter789]

Are you happy to consider using Power Query?
The general approach would be to un-pivot the first table then merge with the second matching on Rank Group and Metric, multiply by the weighting, re-pivot then add weighted scores for a total.

Peter