Forum Discussion
StdDev By Subgroups
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.
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
2 Replies
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
- 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 chartSTDEV =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))
