topic How can I find out why standard deviation measure doesn't work? in DAX Commands and Tips

How can I find out why standard deviation measure doesn't work?

brickanalyst — Wed, 21 Aug 2024 21:18:36 GMT

Hello Everyone,

I have 3 tables and appended on the top of each other. Each table I can identify with table_type column, so that I can use necessary aggregations by filtering them. They have attribute and value column so I can fetch the data in dax (please see below)

VAR _table =

ADDCOLUMNS(

'Appended Table',

"IsNumeric", IF( ISERROR( VALUE('Appended Table'[Value]) ), 0, VALUE('Appended Table'[Value]) ),

"Table Type", 'Appended Table'[Table_Type],

"UID", 'Appended Table'[Attribute]

)

VAR _measure = STDEVX.P(

FILTER(_table, [Table Type]= "My Table"),

[IsNumeric]

)

RETURN _measure

okay I realize it works when I filter one category which values are

0.170289855 and 0.239130435 and their standard deviation is 3.44%

and when I filter another category which values are

0
0.006122449
0.020408163
-0.037037037
-0.048247999
-0.092592593
-0.100040016
-0.123193614
-0.145365551
0.183673469
-0.195918367
0.285714286
0.287504476
-0.306122449
-0.322942857
0.36122449
-0.413793103
-0.624752187

and measure returns = 6.05% however when I look at excel and calculated std.p = 24.68%

I need to figure it out somehow... Since I'm sure I'm working on the right selected (filtered data) and not able to dissect the formula and try to replicate step by step...

I need your help= any ideas, directions will be much appreciated.
thank you !

Re: How can I find out why standard deviation measure doesn't work?

Sahir_Maharaj — Fri, 23 Aug 2024 11:01:42 GMT

Hello brickanalyst,

Can you please try the following approach:

VAR SelectedTable = FILTER( ADDCOLUMNS( 'Appended Table', "IsNumeric", IF( ISERROR( VALUE('Appended Table'[Value]) ), BLANK(), VALUE('Appended Table'[Value]) ) ), [Table Type] = "My Table" && NOT(ISBLANK([IsNumeric])) ) VAR MeanValue = AVERAGEX(SelectedTable, [IsNumeric]) VAR VarianceValue = AVERAGEX(SelectedTable, ([IsNumeric] - MeanValue) * ([IsNumeric] - MeanValue)) VAR StdDeviation = SQRT(VarianceValue) RETURN StdDeviation

Hope this helps!

Re: How can I find out why standard deviation measure doesn't work?

Anonymous — Mon, 26 Aug 2024 05:34:37 GMT

Hi brickanalyst ,

I'm checking how the things are going on about this issue. Whether the Sahir_Maharaj's answer helps you?

If you find any answer is helpful to you, please remember to accept it.

It will help others who meet the similar question in this forum.

If there is any problem, please feel free to let us know.

Thank you for your understanding!

Re: How can I find out why standard deviation measure doesn't work?

brickanalyst — Wed, 28 Aug 2024 20:37:42 GMT

Sahir_Maharaj

Hey Sahir, Thank you for reaching out!
I used your measure on my report, it returns the same records with the measure I shared...

There is one thing I'm CERTAIN
If we don't have any negative values => Std deviation WORKS
If we have one single negative value=> It doesn't

For your information Anonymous