Standard Deviation of a Measure

[PurpleWave22]

I'm hoping to get some help on how to get the standard deviation of a measure. The intent is to add a flat line of 3x the standard deviation of the complaint rate to this graph:

For reference, the complaint rate calculation is this:

Complaint Rate =
'Complaints Report'[Complaints]
    / SUM ( 'Case Report'[Total Cases] )

While "Complaints" is simply:

Complaints =
CALCULATE (
    DISTINCTCOUNT ( 'Complaints Report'[Quality Number] ),
    'Complaints Report'[Is this a complaint?] = "Yes"
) + 0

My attempt at the standard deviation calculation:

Complaint Rate StdDev =
CALCULATE (
    STDEVX.S ( 'Complaints Report', [Complaint Rate] ),
    ALLSELECTED ()
)

The above is close to what I'm looking for, but upon double checking the math doesn't seem correct, as it is yielding 0.1368% while calculating it in excel and by hand yields 0.1158%:

I suspect the formula is calculating on a different context than what I want, but I can't figure out how to make it calculate based on the timeframe that is on the graph, which may be in months or quarters and may be more or less than a year in total length.

Any help or advice would be appreciated!

[tamerj1 · Accepted solution]

Hi @PurpleWave22 

please try

Complaint Rate StdDev =
CALCULATE (
STDEVX.S ( VALUES ( 'Date'[YearMonth] ), [Complaint Rate] ),
ALLSELECTED ()
)

[PurpleWave22]

Hi @tamerj1 

This seems to have worked! Can you explain the logic behind it? I'm not sure I completely understand.

[tamerj1]

@PurpleWave22 

The X-Aggregators in dax are iterators. The need to iterate the right table at the right granularity. 

[Greg_Deckler]

@PurpleWave22 This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.