Forum Discussion

jmfillman's avatar
jmfillman
Helper I
2 years ago

Help With Complex Standard Deviation

I have a table of data, like the below sample data and trying to write a measure from the parent table for Item ID.   I need to calculate Standare Deviation based on the individual Plan ID, where S...
jmfillman's avatar
jmfillman
Helper I
2 years ago

Hi @tailspin,

 

Measure 2 doesn't reference Measure 1, so seems you are proposing only Measure 2?

 

I've done some general reading on the Web on topics related, and seems a formula that does the following will work:
Square Rool of the squared sums of the STDV of each resource plan.

 

Square Root (STDV.P(Plan A^2)+STDV.P(Plan B^2)+etc...)

talespin's avatar
talespin
Solution Sage
2 years ago

hi jmfillman 

 

"Square Root of the squared sums of the STDV of each resource plan."

Used below measure to implement above statement.

 

Measure Used

---------------------------------------------------------------------------------------------------

Weighted SD =
VAR _SelectItem = SELECTEDVALUE(SD[Item ID])
VAR _SummTbl =
ADDCOLUMNS(
    SUMMARIZE(ALL(SD), SD[Item ID], SD[Plan ID]),
    "@Count", CALCULATE( COUNT(SD[Plan ID]), ALLEXCEPT(SD, SD[Item ID], SD[Plan ID]), SD[State] <> "Cancelled"),
    "@STDDEV", CALCULATE( STDEV.P(SD[Hours]), ALLEXCEPT(SD, SD[Item ID], SD[Plan ID]), SD[State] <> "Cancelled")
)
--VAR _CountPlanByItem = SUMX( FILTER(_SummTbl, [Item ID] = _SelectItem), [@Count] )
VAR _SD = SUMX( FILTER(_SummTbl, [Item ID] = _SelectItem), [@STDDEV]*[@STDDEV] )

RETURN SQRT(_SD)
---------------------------------------------------------------------------------------------------

 

Based on what I have read on web

When calculating SD, you use mean in the SD formula.

When calculating Weighted SD, you need to use weighted mean instead of simple mean. I am unable to fully understand the formula which I read on web, that is where I am stuck.

 

Measure 2 doesn't reference Measure 1, so seems you are proposing only Measure 2?

Measure1 was just for testing. Measure2 gives you SD at item level, but it is not weighted SD.