Weighted Measure - Different Criterias

bolwahnn

New Member
Joined
Apr 29, 2011
Messages
40
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:
dDateRankGroupFull NameProduction%Quality%Adherence%
Friday, June 28, 2019BaltimoreMickey Mouse106%100%
Thursday, June 27, 2019BaltimoreMickey Mouse111%100%
Monday, July 1, 2019BaltimoreMickey Mouse110%100%
Monday, July 1, 2019MesaDonald Duck97%100%94%
Thursday, June 27, 2019MesaDonald Duck130%100%50%
Friday, June 28, 2019MesaDonald Duck72%100%94%

<tbody>
</tbody>

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).
RankGroupMetricScoring WeightOld/Current Metric
BaltimoreQuality60%Current
BaltimorePOP40%Current
MesaSchedule Adherence10%Current
MesaQuality50%Current
MesaPOP40%Current

<tbody>
</tbody>

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!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
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).
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top