Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate Standard deviation from a measure

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

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        v-kkf-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        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.