Calculating Standard Deviation on Aggregate Values

Anonymous — Wed, 19 Oct 2022 20:00:44 GMT

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.

Re: Calculating Standard Deviation on Aggregate Values

daXtreme — Wed, 19 Oct 2022 22:22:05 GMT

Use the other version of STDEV -> STDEVX.P or STDEVX.S. These are iterators that can work on any caclulated values.

Re: Calculating Standard Deviation on Aggregate Values

Anonymous — Thu, 20 Oct 2022 15:26:15 GMT

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.

topic Calculating Standard Deviation on Aggregate Values in DAX Commands and Tips

Calculating Standard Deviation on Aggregate Values

Re: Calculating Standard Deviation on Aggregate Values

Re: Calculating Standard Deviation on Aggregate Values