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...
talespin's avatar
talespin
Solution Sage
2 years ago

hi jmfillman 

 

Please see if this is what you require.

The logic for 

Measure1 - SD formula at Product level.

Measure2 - SD formula at Item level(All Products).

Measure3 - Please ignore, Removed measure 3, The formula I used for Weighted SD is not correct. Will add once I understand the formula.

 

 

Measure1

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

StdDev Plan = CALCULATE( STDEV.P(SD[Hours]), REMOVEFILTERS(), SUMMARIZE(SD, SD[Item ID], SD[Plan ID]), SD[State] <> "Cancelled" )
 
Measure2
------------------
StdDev Item = CALCULATE( STDEV.P(SD[Hours]), REMOVEFILTERS(), VALUES(SD[Item ID]), SD[State] <> "Cancelled" )
 
 

 

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.