Forum Discussion

Navyaragamm's avatar
Navyaragamm
Frequent Visitor
2 years ago
Solved

Calculation of averages

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  H...
Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    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's avatar
rajendraongole1
Icon for Super User rankSuper User
2 years ago

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!!