Dax Query to Calculate Cp Cpk using sigma estimator as sbar/c4 in PowerBI

[Anonymous]

i am in a very peculiar requirement of calculating cp cpk in powerbi using the formula for sigma estimator as "sbar/c4" check reference link here

My sample data is as given below

DATE Country Name State Type State Name City Lable PO Box No Box Size READINGS Value LSL USL

06-10-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.31	18.2	18.8
09-10-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.428	18.2	18.8
16-10-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.364	18.2	18.8
19-10-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.48	18.2	18.8
22-10-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.49	18.2	18.8
28-10-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.407	18.2	18.8
01-11-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.512	18.2	18.8
17-11-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.506	18.2	18.8
19-11-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.472	18.2	18.8
21-11-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.507	18.2	18.8
22-11-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.596	18.2	18.8
19-12-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.517	18.2	18.8
21-12-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.529	18.2	18.8
22-12-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.481	18.2	18.8
23-12-2019	Dummy	Gaon	37	Cap191002	1	10	READING7	18.634	18.2	18.8
01-01-2020	Dummy	Gaon	37	Cap191002	1	10	READING7	18.451	18.2	18.8
02-01-2020	Dummy	Gaon	37	Cap191002	1	10	READING7	18.375	18.2	18.8
03-01-2020	Dummy	Gaon	37	Cap191002	1	10	READING7	18.403	18.2	18.8
06-01-2020	Dummy	Gaon	37	Cap191002	1	10	READING7	18.404	18.2	18.8
07-01-2020	Dummy	Gaon	37	Cap191002	1	10	READING7	18.54	18.2	18.8
10-01-2020	Dummy	Gaon	37	Cap191002	1	10	READING7	18.614	18.2	18.8
12-01-2020	Dummy	Gaon	37	Cap191002	1	10	READING7	18.668	18.2	18.8
13-01-2020	Dummy	Gaon	37	Cap191002	1	10	READING7	18.548	18.2	18.8
16-01-2020	Dummy	Gaon	37	Cap191002	1	10	READING7	18.497	18.2	18.8
25-01-2020	Dummy	Gaon	37	Cap191002	1	10	READING7	18.577	18.2	18.8
26-01-2020	Dummy	Gaon	37	Cap191002	1	10	READING7	18.517	18.2	18.8
28-01-2020	Dummy	Gaon	37	Cap191002	1	10	READING7	18.481	18.2	18.8
09-03-2020	Dummy	Gaon	37	Cap200303	1	10	READING7	18.499	18.2	18.8
15-03-2020	Dummy	Gaon	37	Cap200303	1	10	READING7	18.429	18.2	18.8
17-03-2020	Dummy	Gaon	37	Cap200303	1	10	READING7	18.347	18.2	18.8
17-03-2020	Dummy	Gaon	37	Cap200303	1	10	READING7	18.59	18.2	18.8
19-03-2020	Dummy	Gaon	37	Cap200303	1	10	READING7	18.433	18.2	18.8
22-03-2020	Dummy	Gaon	37	Cap200303	1	10	READING7	18.478	18.2	18.8
26-03-2020	Dummy	Gaon	37	Cap200303	1	10	READING7	18.569	18.2	18.8
29-03-2020	Dummy	Gaon	37	Cap200303	1	10	READING7	18.701	18.2	18.8
29-03-2020	Dummy	Gaon	37	Cap200303	1	10	READING7	18.496	18.2	18.8
30-03-2020	Dummy	Gaon	37	Cap200303	1	10	READING7	18.548	18.2	18.8
31-03-2020	Dummy	Gaon	37	Cap200303	1	10	READING7	18.604	18.2	18.8
01-04-2020	Dummy	Gaon	37	Cap200303	1	10	READING7	18.558	18.2	18.8
02-04-2020	Dummy	Gaon	37	Cap200303	1	10	READING7	18.63	18.2	18.8

here we need to calculate cp cpk for the city label column as a subgroup,... this is a sample but in reality, there would be 1000s of city labels, 10 different country names, 40 state names. PO box no, Readings and Box size is to be ignored

C4 value is decided to be 0.9727 as subgroups are considered to be 10

I want to know how do I create a DAX query to calculate the new sigma value

my current query is like this

    Cpk  = 
VAR sigma = STDEV.S(Table[Value])
VAR estimatedMean = AVERAGE(Table[Value])
VAR Cplower = (estimatedMean-MIN(Table[LSL Value]))/(3*sigma)
VAR Cpupper = (MAX(Table[USL Value])-estimatedMean)/(3*sigma)
RETURN MIN(Cplower,Cpupper)

the new query can dynamically change as per slicers selected in Powerbi eg country, state type, state name, etc

Please help me out, the condition is very critical

[v-kelly-msft]

Hi  @Anonymous,

The column headers you provide cant match the data below,pls check wheher the headers should be as below:

What is your expected output?If possible,could you pls upload your sample .pbix file?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!