Forum Discussion

Nankaina's avatar
Nankaina
Helper I
3 years ago

Calculating Standard Deviation on Aggregate Values

Hello,

 

For my current project, I have a column of Referral Dates, which I have divided into weeks using the following column:

Referral Week = 
var startdate = FORMAT('2022 Referral Database'[Referral Date] - weekday('2022 Referral Database'[Referral Date],3), "mmm dd")
var enddate = FORMAT('2022 Referral Database'[Referral Date] - weekday('2022 Referral Database'[Referral Date],3) +4, "mmm dd")
return startdate & " - " & enddate

 With this new column, I've charted a line graph which shows the total Number of Referrals per Week. I would like to also include lines showing the Mean and +/- 1 Standard Deviation on this graph, but am having trouble figuring out how to tease out those values. The Mean is easy, I can add an Average Line. But to add the Standard Deviation lines, I need to make a measure which calculates the standard deviation of these aggregate values. STDEV.P only works if the values in the rows are already the aggregates, so it won't work here.

 

I expect that the solution will require GROUPBY or SUMMARIZE functions, but I can't get these to work from my testing. 

2 Replies

  • I looked into STDEVX.P as well. It requires a calculation done on each row, which isn't what I'm looking for. I need to count how many rows are in each week, rather than count how many fit a given criterion. If there's a way to do this with STDEVX.P, I'm not aware of it.