topic Re: Weighted Variance - Help please? in DAX Commands and Tips

Weighted Variance - Help please?

Anonymous — Fri, 19 Jul 2024 01:08:18 GMT

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.

Re: Weighted Variance - Help please?

BeaBF — Fri, 19 Jul 2024 15:43:47 GMT

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

BBF

Re: Weighted Variance - Help please?

Anonymous — Mon, 22 Jul 2024 07:08:38 GMT

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

Re: Weighted Variance - Help please?

Anonymous — Mon, 22 Jul 2024 11:23:35 GMT

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.

Re: Weighted Variance - Help please?

Anonymous — Mon, 22 Jul 2024 11:31:09 GMT

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.