topic Re: Standard Deviation based off of a Measure instead of Column in DAX Commands and Tips

Standard Deviation based off of a Measure instead of Column

ARLFG — Tue, 07 Mar 2023 01:43:05 GMT

Hi PBI Community,

I'm looking to plot a rolling 12 month Standard Deviation of the Complaint Rate line, which is a measure of Complaints Number divided by 3 Month Sales Average (both numerator and denominator are also measures). I'm under the impression that STDEV.S can only be applied to a column, so I'm hoping to get some help on how to work around this. A measure of a calculated column works for single selected Sub Categories but not for combinations of multiple Sub Categories, which is what this report will primarily use.

Thank you!

Re: Standard Deviation based off of a Measure instead of Column

chudson — Tue, 07 Mar 2023 18:53:43 GMT

I'm trying to do something similiar and think the solution has to be somehow using summarize/summarize columns and creating a virtual table to calculate the Standarad Deviation off of. Is that possible or has anyone done that before?

Re: Standard Deviation based off of a Measure instead of Column

ARLFG — Wed, 08 Mar 2023 02:53:00 GMT

tamerj1 any clues to lead us to victory?!

Re: Standard Deviation based off of a Measure instead of Column

tamerj1 — Wed, 08 Mar 2023 03:41:03 GMT

@Hi ARLFG

perhaps someone like this?

STD =
VAR CurrentMonth =
SELECTEDVALUE ( 'Date'[Month] )
RETURN
MAXX (
FILTER (
ADDCOLUMNS ( ALLSELECTED ( 'Date'[Month] ), "@STD", STDEV.S ( [Measure] ) ),
'Date'[Month] = CurrentMonth
),
[@STD]
)