I'm looking for a way to set a weight for different measures in excel. Some background -> I work on a Sales Support team and I'm trying to make a quick dashboard to show sales capacity for each team.
We factor in total sales, locations, potential, and another metric we calculate internally. The weights would be:
Sales - 75%
Locations - 10%
Potential - 10%
Metric - 5%
I would then use that calculation to determine the over capacity for that team.
You could create calculated fields, something like:
SUM([Sales]) * 0.75
SUM([Locations]) * 0.15
Then add them all together in a final calculated field:
[Weighted Sales] + [Weighted Locations] + [Weighted Potential] + [Weighted Metric]