Standard Deviation and Averaging

[j4ymastah]

Good day.

I am trying to get the standard deviation (SD) and average (AVE) of the following data:

LOGIN DATE - Copy Jan 01 2021 Jan 04 2021 Jan 02 2021 Jan 05 2021 Jan 03 2021 Jan 06 2021 Jan 07 2021 Jan 08 2021 Jan 09 2021 Jan 10 2021 Jan 11 2021 Jan 12 2021 Jan 13 2021 Jan 14 2021 Jan 15 2021 Jan 16 2021 Jan 17 2021 Jan 18 2021 Jan 19 2021 Jan 20 2021 Jan 21 2021 Jan 22 2021 Jan 23 2021 Jan 24 2021 Jan 25 2021 Jan 26 2021 Jan 27 2021 Jan 28 2021 Jan 29 2021 Jan 30 2021 Jan 31 2021 Feb 01 2021 Feb 02 2021 Feb 03 2021 Feb 04 2021 Feb 05 2021 Feb 06 2021 Feb 07 2021 Feb 08 2021 Feb 09 2021 Feb 10 2021 Feb 11 2021 Feb 12 2021 Feb 13 2021 Feb 14 2021 Feb 15 2021 Feb 16 2021 Feb 17 2021 Feb 18 2021 Feb 19 2021 Feb 20 2021 Feb 21 2021 Feb 22 2021 Feb 23 2021 Feb 24 2021 Feb 25 2021 Feb 26 2021 Feb 27 2021 Feb 28 2021 Mar 01 2021 Mar 02 2021 Mar 03 2021 Mar 04 2021 Mar 05 2021 Mar 06 2021 Mar 07 2021 Mar 08 2021 Mar 09 2021 Mar 10 2021 Mar 11 2021 Mar 12 2021 Mar 13 2021 Mar 15 2021 Mar 16 2021 Mar 17 2021 Mar 18 2021 Mar 19 2021 Mar 20 2021 Mar 21 2021 Mar 22 2021 Mar 23 2021 Mar 24 2021 Mar 25 2021 Mar 26 2021 Mar 27 2021 Mar 29 2021 Mar 30 2021 Mar 31 2021

Count 1 368 13 421 5 526 243 228 25 8 383 571 464 194 373 45 12 485 713 912 930 1615 161 46 851 835 695 458 471 49 10 372 491 415 238 281 20 1 302 406 461 321 32 20 5 368 386 431 241 367 17 5 358 316 323 8 148 9 1 236 324 364 142 190 11 5 193 324 311 123 185 14 221 313 352 194 931 41 2 293 290 238 95 196 15 220 281 212

I am trying to get SD and AVE  for each month. I tried using 

SD = CALCULATE( STDEVX.P('Sheet1','Sheet1'[Count]), allselected('Sheet1'))

but it computes for the entire data.

Please help. Thanks in advance

[j4ymastah]

And that fixes it! Many thanks, @v-rzhou-msft 

[v-rzhou-msft · Accepted solution]

Hi @j4ymastah 

You can add a YearMonth column in your data model. Then you can achieve your goal by measure or calculate funtion in Table visual directly.

Add a YearMonth column.

YearMonth = FORMAT('Table'[LOGIN DATE - Copy],"YYYYMM")

Measure:

SD Per Month = CALCULATE(STDEV.P('Table'[Count]),FILTER(ALL('Table'),'Table'[YearMonth]=MAX('Table'[YearMonth])))

AVG Per Month = CALCULATE(AVERAGE('Table'[Count]),FILTER(ALL('Table'),'Table'[YearMonth]=MAX('Table'[YearMonth])))

Or use Average/Standard Deviation function in table visual.

Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.