Forum Discussion
Weighted Variance - Help please?
I have a lot of history data and I'm trying to determine both the overall weighted variance amongst all employees as a whole, but also find out each individuals weighted variance.
Averages and a lot of other data i've managed to figure out. Weighted variance, however, is very complicated.
Can someone lend a hand with the right measurements? I'd greatly appreciate it. I can upload a sample of the data if needed. I just took a SS and posted it to show you what it looks like.
4 Replies
Anonymous Can you paste some sample data and the expected output on this data?
BBF
Well, I took a screenshot of the data. I don't post often enough to know exactly which button allows me to supply data. The only thing I changed on the data sample were the names of the call centers and the names of the evaluators to protect employees.
As for the result, I don't have that yet as we're still working with the client to make a determination on that result.
Hi Anonymous
Maybe you could try the following steps:
- My example data is as follows.
- First, I create a new column to assign a weight of 1-5 by tier of scores, e.g. scores >95 are weighted 5, scores >90 are weighted 4, scores >85 are weighted 3, scores >80 are scored 2, and the rest of the scores are weighted 1, with the following DAX:
WEIGHT = SWITCH(TRUE(),'Table'[SCORE]>95,5,'Table'[SCORE]>90,4,'Table'[SCORE]>85,3,'Table'[SCORE]>80,2,1)
- Create weighted variance measures for all evaluators and individual evaluators, respectively, with the following DAX:
WeightedVarianceByEvaluator =
VAR EVALUATOR = VALUES('Table'[NAME])
VAR Result =
SUMX(
EVALUATOR,
VAR CurrentEvaluator = [NAME]
VAR WeightedMean =
CALCULATE(
SUMX('Table', 'Table'[SCORE]*'Table'[WEIGHT])/SUM('Table'[WEIGHT]), 'Table'[NAME] = CurrentEvaluator
)
VAR Varianced =
CALCULATE(
SUMX('Table', ('Table'[SCORE] - WeightedMean)^2 * 'Table'[WEIGHT]), 'Table'[NAME] = CurrentEvaluator
)/CALCULATE(SUM('Table'[WEIGHT]), 'Table'[NAME] = CurrentEvaluator)
RETURN Varianced
)
RETURN Result
OverallWeightVariance =
VAR Weightmean = SUMX('Table', 'Table'[SCORE]*'Table'[WEIGHT])/SUM('Table'[WEIGHT])
VAR VarianceTable =
ADDCOLUMNS(
'Table',
"WeightDifferentSquared", ('Table'[SCORE] - Weightmean)^2 * 'Table'[WEIGHT]
)
RETURN
SUMX(VarianceTable, [WeightDifferentSquared]) / SUMX('Table', 'Table'[WEIGHT])
- Create a Table visual object, put “NAME” and “WeightedVarianceByEvaluator” into it, and create a Card visual object, put “OverallWeightVariance” into it. Here is my final result.
I hope this solution meets your requirements.
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I'm not no sure that's the weighted variance I'm looking for. It shouldn't be that drasticly high.
I don't know how to explain it but a person with 5 evaluations(evaluator 5), their variance won't weigh as much compared to someone who has 11 evaluations(evaluator 7).
I tried basing it on the explanationa and formula based on this: How to Calculate Weighted Variance | Bizfluent
Then it doesn't get the same result as using the variance calculator found here: Variance Calculator (calculatorsoup.com)
We're still working with the client because right now they're not understanding it and I'm not sure I do either.
I'll post back more hopefully later this week once we get a better understanding of it.
The way I'm calculating it now shows a weighted variance of 7.69 for July 1st through July 18th data, but then it drops down to 7.63 after factoring in data from the 19th after I had posted.
- My example data is as follows.
