Weighted Variance - Help please?

[Anonymous]

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.

[Anonymous]

Hi @Anonymous 

Maybe you could try the following steps:

1. My example data is as follows.  
2. 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)  

3. 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])  

4. 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.}

[Anonymous]

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.

[BeaBF]

@Anonymous Can you paste some sample data and the expected output on this data?

BBF

[Anonymous]

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.