Stdev.s giving incorrect value

[nehajadhav166]

Hi Experts,

In my Cpk calculation, I see STDEV.S is giving incorrect value.

Data set I have as below

-11.67
-11.71
-11.67
-11.67
-11.69
-11.65
-11.68
-11.67
-11.63
-11.60
-11.65

I expect value as 

MEAN	-11.6627
STD	0.0297
CpK	37.46

But my Cpk value is 39.18. When I debugged issue, I see std deviation caculation is different . 

Formula I use is 

STDEV.S(RFLoopback[Value]) but value I get for 

STDEV.P(RFLoopback[Value])

This is creating issue.

Does anyone have idea how to resolve this issue?

Thanks,

Neha

[v-jianboli-msft]

Hi @nehajadhav166 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nehajadhav166 · Accepted solution]

@v-jianboli-msft ,

Issue is resolved now. There was data discrepency when I am loading data using M query. I removed some colunms and removed duplicate values. Now its working fine.

There is no issue with STDEV.S and STDEV.P calculation in power BI.

Thanks,

Neha

[Greg_Deckler]

@nehajadhav166 Correct.

[nehajadhav166]

@Greg_Deckler ,

This is strange.I use below formula.

CpkLoop =

VAR sigma = STDEV.S(RFLoopback[Value])

VAR estimatedMean = AVERAGE(RFLoopback[Value])

VAR Cplower = (estimatedMean-MIN(RFLoopback[LSL]))/(3*sigma)

VAR Cpupper = (MAX(RFLoopback[USL])-estimatedMean)/(3*sigma)

RETURN IFERROR(MIN(Cplower,Cpupper),1e6)

Still STDEV.S give me 0.0281.

Any idea what could be the issue?

Thanks,

Neha

[Greg_Deckler]

@nehajadhav166 Mystery. See my attached PBIX below sig.

[Greg_Deckler]

@nehajadhav166 How is CPK being calculated? Your formula. If I use STDEV.S I get back 0.0297 whereas STDEV.P returns 0.02831. So use STDEV.S?

[nehajadhav166]

@Greg_Deckler , yes I am using STDEV.S.

How did you calculate? in power BI?

Thanks,

Neha