StdDev By Subgroups

[PhilipH]

Hi guys,

I am hoping you can help as I am going in circles.

I am looking to calculate the stdev for a [data] column, grouped according to a [VarFtrID] column and then a [Sequence] column. 

If I put a Card on the Report page how can I get it to display the stddev correctly? I can use a slicer to select the correct [VarFtrID] but then my data is sometimes subgrouped, where the same [Sequence] number will be repeated throughout the subgroup.

Any help greatly appreciated.

[Anonymous · Accepted solution]

Hi  @PhilipH ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

The standard deviation of the entire population

the standard deviation of the entire population =
CALCULATE(STDEV.P('Table'[data]),FILTER(ALL('Table'),'Table'[VarFtrID]=EARLIER('Table'[VarFtrID])&&'Table'[Sequence]=EARLIER('Table'[Sequence])))

Refer to :

STDEV.P function (DAX) - DAX | Microsoft Docs

The standard deviation of a sample population

the standard deviation of a sample population. = CALCULATE(STDEV.S('Table'[data]),FILTER(ALL('Table'),'Table'[VarFtrID]=EARLIER('Table'[VarFtrID])&&'Table'[Sequence]=EARLIER('Table'[Sequence])))

Refer to :

STDEV.S function (DAX) - DAX | Microsoft Docs

2. Result:

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

[PhilipH]

Thank you for your reply. The built in formula are slightly different to the calcs we need to use so I think I will need to manually calculate it. I think I've got the dev_sq_sum bit wrong though. This is to show the value per row according to subgoup in a chart

STDEV =

var x_sum = CALCULATE(SUM('VarData'[data]),FILTER(ALL('VarData'),'VarData'[VarFtrID]=EARLIER('VarData'[VarFtrID])))

var x_count = CALCULATE(COUNT('VarData'[data]),FILTER(ALL('VarData'),'VarData'[VarFtrID]=EARLIER('VarData'[VarFtrID])))

VAR mean = (x_sum / x_count)

VAR dev_sq_sum = CALCULATE(sumx('VarData',POWER(VarData[Data] - mean, 2)),FILTER(ALL('VarData'),'VarData'[VarFtrID]=EARLIER('VarData'[VarFtrID])))

VAR std_dev = SQRT(dev_sq_sum/(x_count-1))