topic Re: Help With Complex Standard Deviation in DAX Commands and Tips

Help With Complex Standard Deviation

jmfillman — Sat, 16 Mar 2024 22:14:27 GMT

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 State <> Cancelled that is then aggregated together to get a Standard Deviation for the all the Plans ID's for each Item ID.

Taking the first Item ID (12345), I need the STDV of Plan A for Feb, Mar, and Apr (Jan excluded because it is Cancelled), then for Plan B and Plan C, but the value I need to return is, what I see as a weighted average of the STDV for Plan A, Plan B, and Plan C or whatever gives me the overall STDV weighted by the variability of months of each plan, and then again the same approach for each subsequent Item ID.

Item ID	Plan ID	Date	Hours	State
12345	A	Jan	5	Cancelled
12345	A	Feb	1	Complete
12345	A	Mar	4	Active
12345	A	Apr	5	Active
12345	B	Jan	10	Complete
12345	B	Feb	8	Active
12345	B	Mar	9	Cancelled
12345	B	Apr	5	Active
12345	C	Feb	5	Complete
12345	C	Mar	5	Active
54321	AA	Jan	5	Complete
54321	AA	Feb	1	Complete
54321	AA	Mar	4	Active
54321	AA	Apr	5	Active
54321	BB	Jan	10	Complete
54321	BB	Feb	8	Complete
54321	BB	Mar	9	Cancelled
54321	BB	Apr	5	Active
54321	CC	Feb	5	Complete
54321	CC	Mar	5	Active

Re: Help With Complex Standard Deviation

lbendlin — Sat, 16 Mar 2024 23:18:41 GMT

There is not enough data to make this statistically significant. How many rows are in your actual data?

Re: Help With Complex Standard Deviation

jmfillman — Sun, 17 Mar 2024 00:04:38 GMT

This is just sample data. Each plan can have 1 to n rows and each Item ID can have 1 to n plans. Actual data is currently about 11,000 rows, but varies month-to-month. Ultimately, I need a value to indicate whether month-to-month hours are nearly the same, by plan, or have significant variability, where higher variability between months of the same Plan ID and for the Item ID is a good thing, where little or no variability is not good.

Re: Help With Complex Standard Deviation

talespin — Sun, 17 Mar 2024 13:43:23 GMT

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" )

Re: Help With Complex Standard Deviation

jmfillman — Sun, 17 Mar 2024 20:39:30 GMT

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...)

Re: Help With Complex Standard Deviation

talespin — Mon, 18 Mar 2024 03:24:41 GMT

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.