Power Bi mix calculation between aggregators and iterators

[Pietro]

Hi all,

I have to calculate the weighted average and standard deviation of my data.

I basically have the wind farm locations, age, Generator Make, and the number of generators.

Location	Age	Generator Make	N. Generator
A	23.16	Bonus	14
B	17.65	Bonus	21
C	19.33	Bonus	2
D	26.66	Bonus	26
E	24.74	Bonus	34

As there might be from 1 up to 30 generators on each farm, a simple average of the age would not result in a correct indicator. I also want to know the spread of the age, hence the need to calculate the weighted standard deviation.

I can calculate the weighted average age in DAX.

I cannot find a way of calculating the weighted variance or standard deviation of the age of the turbines across the locations, as it requires the use of iterator formulae, along with either aggregator formula or measure, i.e. the calculated weighted average.

I would really like not to have to learn another language such as python and re-do the whole work.

Regards,

Pietro

[OwenAuger · Accepted solution]

Hi @Pietro 

You can certainly calculate a weighted variance or standard deviation, making use of iterators.

Here is how I would create measures for this, starting from scratch. I've assumed your table is called Generators and has the columns shown in your example.

Link to the variance formula I've applied:

https://en.m.wikipedia.org/wiki/Weighted_arithmetic_mean#Frequency_weights

# Generators = 
SUM ( Generators[N. Generator] )

Weighted Average Age = 
VAR NumGenerators =
    [# Generators]
VAR WeightedSumOfAge =
    SUMX (
        Generators,
        Generators[Age] * Generators[N. Generator]
    )
VAR Result =
    DIVIDE ( WeightedSumOfAge, NumGenerators )
RETURN
    Result

Weighted Variance of Age = 
VAR NumGenerators =
    [# Generators]
VAR WeightedAverageAge =
    [Weighted Average Age]
VAR SumOfWeightedSquaredDeviation =
    SUMX (
        Generators,
        Generators[N. Generator] * ( Generators[Age] - WeightedAverageAge ) ^ 2
    )
VAR NumGeneratorsMinusOne =
    NumGenerators - 1
VAR Result =
    DIVIDE ( SumOfWeightedSquaredDeviation, NumGeneratorsMinusOne )
RETURN
    Result

Weighted Standard Deviation of Age = 
SQRT ( [Weighted Variance of Age] )

The Variance and Standard Deviation measures should produce the same result as if each generator was listed separately on its own row, and a sample variance function (VAR.S or VARX.S) was applied.

Does this work at your end?

Regards,

Owen