Question about DAX Standard Deviation function

[Guinaba]

Hi experts,

Not sure why I am getting different results when calculating standard deviation in Excel and DAX using the same data sample.

In Excel using STDEV.S(C9:N9) the result is 2176

Mth_1	Mth_2	Mth_3	Mth_4	Mth_5	Mth_6	Mth_7	Mth_8	Mth_9	Mth_10	Mth_11	Mth_12	StDev
12118	8059	9180	6352	6863	6797	8548	7657	8845	7063	6352	2916	2176

Using DAX function STDEVX.S the result is 342

STD (12mth):=STDEVX.S(RawData,CALCULATE([Mth1]+[Mth2]+[Mth3]+[Mth4]+[Mth5]+[Mth6]+[Mth7]+[Mth8]+[Mth9]+[Mth10]+[Mth11]+[Mth12])) = 342

Gilly

 

[karim muhtasib]

I transposed the original data set into the following table and imported into PP:

The standard deviation function then becomes a simple:

Std Dev:=STDEV.S(STD[Amount]) = 2,176.02

I noticed the function worked better for me calculating straight down on a single measure (amount) then it did across multiple measures.

 

[Guinaba]

Thanks @karim muhtasib! Good catch!!
Would you know how can I transpose this data to achieve your result? I am using Power Pivot (Excel 2013) so I don't have selectcolumn function available

 

[karim muhtasib]

the cleanest way would to do it would be at the data source level and bring back into your data model. delete the the previous connection and re-import the updated data source (after the manual copy- paste special- transpose treatment).