cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
j4ymastah
New Member

Standard Deviation and Averaging

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
1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

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.

1.png

Result is as below.

2.png

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. 

 

View solution in original post

2 REPLIES 2
j4ymastah
New Member

And that fixes it! Many thanks, @RicoZhou 

RicoZhou
Community Support
Community Support

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.

1.png

Result is as below.

2.png

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. 

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.