Calculate Standard deviation from a measure

[Anonymous]

Hello, 

I appreciate that there are a few articles on here however,frustratingly I am yet to find one that solves my query.

Essentially I need to calculate the Standard deviation from a DAX measure as I am looking to calculate the count of incidents. To calculate the count of incidents I use the following Measure:

Count Of incidents = COUNTA('ssc_db_001 ga_incident_report'[id])

Now I would like to count the standard devation by store & Incident type.

I have used the following Formulas, to no effect.

(Test)Standard Deviation count of incidents = STDEVX.P('ssc_db_001 ga_incident_report',[Count Of incidents])

stdevm = CALCULATE(STDEVX.P(VALUES('ssc_db_001 ga_incident_report'[Incident type]),CALCULATE(COUNT('ssc_db_001 ga_incident_report'[id]))))

SD Test 2 var = 
   var countofincicent=[Count Of incidents]

   Return CALCULATE(STDEVX.P('ssc_db_001 ga_incident_report',[Count Of incidents]))

I recieve an output from Stdevm in regards to a store output however it is wrong- When I collapse the periods, the first store has a SD of 119, from P01-P06 however it should be 21.

STdevm does not put an output for incident type however it does total at the bottom- This may be due to the filters but im unsure?

I would need the solution to be dynamic/react to filter context. If anyone could help Id be forever in your debt!

Kind regards,

Alex

[Greg_Deckler]

@Anonymous 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.

[Anonymous]

Hi Greg, 

Thank you for your response. I have addapted the measure however I am still getting the same results- Obviously I am going wrong somehwere but I am unsure where.

Any further guidance would be great!

Thank you for your time & help!

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Since you are using the iterator function STDEVX.P, you can refer to this post to understand the context in the iterator function.

If you want to calculate the standard deviation for different periods and different stores, you can use the following formula.

Measure = 
IF(
    ISFILTERED('ssc_db_001 ga_incident_report'[Period]), 
    STDEVX.P( 
        VALUES('ssc_db_001 ga_incident_report'[Incident type]), 
        CALCULATE( COUNTA('ssc_db_001 ga_incident_report'[id]) ) 
    ),
    IF(
        ISFILTERED('ssc_db_001 ga_incident_report'[Store name]),
        STDEVX.P( 
            VALUES('ssc_db_001 ga_incident_report'[Period]), 
            CALCULATE( COUNTA('ssc_db_001 ga_incident_report'[id]) ) 
        ),
        STDEVX.P( 
            VALUES('ssc_db_001 ga_incident_report'[Store name]), 
            CALCULATE( COUNTA('ssc_db_001 ga_incident_report'[id]) )
        )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.