How to calculate Standard deviation with aggregated column and dynamic filters

[Panu_U]

Hello,

My apologies if this type of inquiry has already been posted and solved.

I tried to look for the solution and searched in the forum but I couldn't find it.

I would like to:

- Calculate Standard Deveiation (aggregated data by month)

- Return Standard Deviation to all rows

- Would like the calculation to be dynamic according to selected filters.

I tried many codes but it didn't seem to work.

For example, 

N_STD =

var group_month = summarize(Dim_Date,Dim_Date[Month-Year], "sum_by_month" , sum(volume_fact[Total_volume]))

var stdev = calculate(stdevx.P(group_month, [sum_by_month]), allselected())

return(stdev)

The following is the example of the dataset:

Month-Year	Department	Product	Volume
2/1/2022	AL	REG	120,002
2/1/2022	AL	VIRTUAL	115,820
2/1/2022	CA	REG	230,142
2/1/2022	CA	VIRTUAL	105,874
3/1/2022	AL	REG	114,525
3/1/2022	AL	VIRTUAL	104,524
3/1/2022	CA	REG	235,741
3/1/2022	CA	VIRTUAL	121,025
4/1/2022	AL	REG	102,458
4/1/2022	AL	VIRTUAL	145,021
4/1/2022	CA	REG	211,021
4/1/2022	CA	VIRTUAL	114,528

I expect to display the information as below:

Month-Year	Volume	STD
2/1/2022	571,838	1,667
3/1/2022	575,815	1,667
4/1/2022	573,028	1,667

If I select a filter (department = "AL"), I expect the calculation to be changed as below: 

Month-Year	Volume	STD
2/1/2022	235,822	11,669
3/1/2022	219,049	11,669
4/1/2022	247,479	11,669

Thank you in adavnce and I appreciate any help you could provide.

Thank you,

[v-yangliu-msft · Accepted solution]

Hi  @Panu_U ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _select=SELECTEDVALUE('Table'[Department])
return
IF(
    HASONEVALUE('Table'[Department]),
SUMX(FILTER(ALL('Table'),
MONTH('Table'[Month-Year])=MONTH(MAX('Table'[Month-Year]))&&'Table'[Department]=_select),[Volume]),
SUMX(FILTER(ALL('Table'),
MONTH('Table'[Month-Year])=MONTH(MAX('Table'[Month-Year]))),[Volume]))

Measure 2 =
STDEVX.P(ALLSELECTED('Table'),[Measure])

2. Result:

If you need pbix, please click here.

Best Regards,

Liu Yang

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

[Panu_U]

Hi Liu,

Thank you so much.

I really appreciate it.

Thank you,

Panu