Urgent- Need to calculate the Standard deviation within Subgroups

[Abhijeet_21]

Hi,
How can we calculate the Standard deviation within subgroup in power bi?
The standard deviation inbuilt formulae may not work since I need to calculate the standard deviation within subgroups whereas in build formulae will give results as overall.
Thank you in advance!

[Kishore_KVN]

Hello @Abhijeet_21 ,

You can try below DAX Measure it may work:

SDWithinGroup = 
    CALCULATE(
        STDEVX.P(
            VALUES(Table_Name[Subgroup_Name]),
            CALCULATE(SUM(Table_Name[Value]))
        )
    )

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

[Abhijeet_21]

Hi @Kishore_KVN ,

Thank for your quick reply!!

Actually I have tried your formalue, its not working and giving the expected results. let me try to explain the Standard deviation within Subgroup using simple example along with expected output. It may be helpful for you to share me Power BI solution for same.

Below is reference data along with output mentioned in column "Moving range". The Final output is "SD Within Subgroup value".

Can you please share the Measure for this now?

2 Sepearate measure are also (Moving Range & SD within Subgroup) fine so that I can use in table/graphs.

Thanks again!

Abhijeet

[Abhijeet_21]

Hi @Kishore_KVN ,

Thank for your quick reply!!

Actually I have tried your formalue, its not working and giving the expected results. let me try to explain the Standard deviation within Subgroup using simple example along with expected output. It may be helpful for you to share me Power BI solution for same.

Below is reference data along with output mentioned in column "Moving range". The Final output is "SD Within Subgroup value".

Year	Month	Day	SN	TYPE	VALUE	Moving Range
2002	February	1	A3624	MIN	98.0185
2002	February	2	A3625	MIN	98.0145	0.004
2002	February	2	A3626	MIN	98.0161	0.0016
2002	February	6	A3434	MIN	98.0193	0.0032
2002	February	6	A3435	MIN	98.0159	0.0034
2002	February	6	A3436	MIN	98.0162	0.0003
2002	February	6	A3499	MIN	98.0188	0.0026
2002	February	7	A3627	MIN	98.0194	0.0006
2002	February	8	A3628	MIN	98.0223	0.0029
2002	February	8	A3629	MIN	98.0195	0.0028
2002	February	9	A3630	MIN	98.0193	0.0002
2002	February	13	A3500	MIN	98.0213	0.002
2002	February	15	A3638	MIN	98.0153	0.006
2002	February	16	A2659	MIN	98.0189	0.0036
2002	February	16	A3525	MIN	98.0174	0.0015
2002	February	16	A3526	MIN	98.0159	0.0015
2002	February	17	A3607	MIN	98.0213	0.0054
2002	February	19	A3640	MIN	98.0106	0.0107
2002	February	21	A3642	MIN	98.0167	0.0061
2002	February	21	A3643	MIN	98.0138	0.0029
2002	February	22	A3644	MIN	98.0149	0.0011
2002	February	22	A3645	MIN	98.0153	0.0004
2002	February	23	A3647	MIN	98.0171	0.0018
2002	February	23	A3698	MIN	98.0151	0.002
2002	February	27	A3646	MIN	98.0185	0.0034
2002	February	27	A3648	MIN	98.0176	0.0009
2002	February	27	A3649	MIN	98.0189	0.0013
2002	February	27	A3699	MIN	98.0205	0.0016
2002	February	28	A3637	MIN	98.0157	0.0048
2002	February	28	A3641	MIN	98.0145	0.0012
2002	February	28	A3650	MIN	98.021	0.0065
2002	February	28	A3651	MIN	98.0206	0.0004

Sum of Moving Range	0.0867
Average Moving Range	0.002796774
D2 (Control Chart Factor for N=2)	1.128	Fixed Value from Reference Table

SD Within Subgroup =	(Average Moving Range/D2)
SD Within Subgroup value	0.00247941

Can you please share the Measure for this now?

2 Sepearate measure are also (Moving Range & SD within Subgroup) fine so that I can use in table/graphs.

Thanks again!

Abhijeet