Weighted Variance - Help please?

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