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

[Anonymous]

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:

ID	Value
A	2
A	3
B	4
B	5
C	6
C	7

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

ID	Sum
A	5
B	9
C	13

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/

[daxer-almighty]

Here are your measures:

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

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

[Anonymous]

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]

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?