Calculation of averages

[Navyaragamm]

Hi , 

I am stuck with how to calculate the averages while doing a calculated column 

So , i want result> (average+2*std.dev) and result<(average -2*std.dev) if both are true then yes else no 

Here result is a column called data[Value] and average is of the same column , also the standard deviation

The average here is average of some components and result is value in each row 

Below are the example of calculations , i tried doing a calculated column but all the answers are no . is there a right way to do it , should I use calculated column or measures , Can anyone help 

Here NaB free such are components so the example calculation is 0.4 >0.21+2* 0.269 

0.4 is the value of the row

0.21 is the average of that lot number 

0.269 is the std.dev of the lot number

[Sahir_Maharaj · Accepted solution]

Hello @Navyaragamm,

Can you please try this approach:

LotAverage = 
CALCULATE(
    AVERAGE(Data[Value]),
    ALLEXCEPT(Data, Data[LotNumber])
)

LotStdDev = 
CALCULATE(
    STDEV.P(Data[Value]),
    ALLEXCEPT(Data, Data[LotNumber])
)

OutOfRange = 
VAR AvgValue = CALCULATE(
    AVERAGE(Data[Value]),
    ALLEXCEPT(Data, Data[LotNumber])
)
VAR StdDevValue = CALCULATE(
    STDEV.P(Data[Value]),
    ALLEXCEPT(Data, Data[LotNumber])
)
RETURN
IF(
    Data[Value] > AvgValue + 2 * StdDevValue || Data[Value] < AvgValue - 2 * StdDevValue,
    "Yes",
    "No"
)

[rajendraongole1]

Hi @Navyaragamm - create two measures to calculate average and std

measure1:

Avg_Value = CALCULATE(AVERAGE('data'[Value]), ALLEXCEPT('data', 'data'[Component]))

Measure2:
StdDev_Value = CALCULATE(STDEV.P('data'[Value]), ALLEXCEPT('data', 'data'[Component]))

 create a new calculated column to find the std deviation to check the condition as below:

Result_Check =
VAR Avg = CALCULATE(AVERAGE('data'[Value]), ALLEXCEPT('data', 'data'[Component]))
VAR StdDev = CALCULATE(STDEV.P('data'[Value]), ALLEXCEPT('data', 'data'[Component]))
RETURN
IF(
AND(
'data'[Value] > Avg + 2 * StdDev,
'data'[Value] < Avg - 2 * StdDev
),
"Yes",
"No"
)

Hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!