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:   ...
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?