Forum Discussion

PhilipH's avatar
PhilipH
New Member
4 years ago
Solved

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.   ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
PhilipH
New Member
4 years ago
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))