Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PhilipH
New Member

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.

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @PhilipH ,

I created some data:

vyangliumsft_0-1656552548943.png

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:

vyangliumsft_1-1656552548945.png

 

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

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi  @PhilipH ,

I created some data:

vyangliumsft_0-1656552548943.png

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:

vyangliumsft_1-1656552548945.png

 

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 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))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.