Standard Dev of count of data (monthly)

[nakul555]

I have data in power bi in the table in below form:

ID	Date	Failure
1	1/1/2021	A
2	1/2/2021	B
3	1/17/2021	C
4	2/4/2021	A
5	2/15/2021	D
6	4/6/2021	D
7	7/10/2021	C
8	8/12/2021	B
9	9/9/2021	A
10	12/30/2021	E

And I need to calculate standard deviation of individual failure (for A,B,C....) counts in past 12 months.

Failure Counts	Apr	May	Jun	Jun	Jul	July	Aug	Sep	Standard Dev
A	1	5	3	5	6	7	8	9	=+STDEV.S(J21:Q21)
B	2	7	5	8	9	11	12	14	=+STDEV.S(J22:Q22)
C	5	4	5	6	9	1	7	2	=+STDEV.S(J23:Q23)
D	4	1	3	0	8	3	4	5	=+STDEV.S(J24:Q24)
E	3	1	3	6	7	9	0	1	=+STDEV.S(J25:Q25)

Something like this

Failure	Std Dev
A	2.5
B	2.5
C	3.5
D	4
E	1.2

Can someone please help me?

[selimovd]

Hey @nakul555 ,

that should be possible with the function STDEV.S

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[nakul555]

Hello @selimovd,

The issue is how to calculate the count monthly so that its standard dev can be calculated.

Currently I have 

Failure Code Monthly = CALCULATE(COUNTX(FirstTable, 'FirstTable'[ID]), DATESINPERIOD('Work Detail'[Date],EOMONTH(TODAY(),-1),-12,MONTH))

Std Dev = VAR SD = STDEVX.S(VALUES('FirstTable'[Month]),[Failure Code Monthly])

RETURN

IFERROR(SD,0)

And it not giving the correct result