Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Help - Average and Standard Deviation from a Summarized Column based on Another

Hi,

 

I would like to request your help with the following inquiry, I need to create two measures that allow me to calculate the Average and Standard Deviation based on the following scenario:

 

This would be my raw data:

 

IDValue
A2
A3
B4
B5
C6
C7

 

The first step would be to calculate the summarized value of each ID:

 

IDSum
A5
B9
C13

 

And then from that second table, I need to create the two measures to calculate:

 

1- Average = 9

2- Standard Deviation = 4

 

I would appreciate any help or guidance that you can provide me/

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello Anonymous ,
    You can simply group by in power query for the summarized value.
    Use below measures for the calculation.
    Average = 

    Avg =
    VAR TotalSum = SUM('Avg and SD'[Value])
    VAR TotalID = COUNT('Avg and SD'[ID])
    RETURN
    DIVIDE( TotalSum, TotalID)

    SD = STDEV.P('Avg and SD'[Value])
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous,

       

      Thank you very much for your help, to calculate the average I used this measure:

       

      Average =
      VAR TotalSum = SUM('Table'[Value])
      VAR TotalID = DISTINCTCOUNT('Table'[ID])
      RETURN
      DIVIDE( TotalSum, TotalID)
       
      I was not understanding why you defined these sections: SUM('Avg and SD'[Value]) and COUNT('Avg and SD'[ID]) like you did.
       
      But I am not able to correctly calculate the standard deviation, since the formula needs a column and you are defining it like this:
       
      SD = STDEV.P('Avg and SD'[Value])
       
      Could you please let me know what would be the sintax of the measure?
  • daxer-almighty's avatar
    daxer-almighty
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Here are your measures:

     

    [Average] =
AVERAGEX(
    DISTINCT ( T[ID] ),
    CALCULATE( SUM[Value] )
)

[Standard Dev.] =
STDEVX.S(
    DISTINCT ( T[ID] ),
    CALCULATE( SUM[Value] )    
)