Calculating Average of a COUNT

[npowers2]

I am working on creating a p-chart for my data for metric tracking of defects that we identify. I have been able to calculate the proportion for each variable using the following measure:

Proportion = 
VAR Defects = AVERAGEX(SUMMARIZE(Query1, Query1[Profile Result], "_Defects", CALCULATE(COUNT(Query1[SampleNumber]))),[_Defects])

VAR Tested = CALCULATE(COUNTA(Query1[SampleNumber]), ALLEXCEPT(Query1, Query1[Week]))

Return
Defects / Tested

I have been having issues with generating the necessary measures for p-bar and my control limits:

pbar = CALCULATE([SUM Defects] / [SUM Tested], ALLSELECTED(Query1))

UCL = [pbar] + 3*SQRT(([pbar]*(1-[pbar])) / Average(Query1[Tested]))

The issue that I am running into is not being able to determine how to SUM all results for the entire data set as a SUM TESTED result and SUM all Results based on [Profile Result] as a SUM TESTED result. 

Any help would be greatly appreciated. 

[Anonymous]

Hi @npowers2 ,

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.

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @npowers2 ,

Not very clear.

If want to sum values of each Profile Result? You could use the following formula:

SUM based on Profile Result =
CALCULATE (
    SUM ( Query1[Column] ),
    FILTER ( 'Query1', 'Query1'[Profile Result] = MAX ( 'Query1'[Profile Result] ) )
)

Please describe in more detail the result you want.

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

[amitchandak]

@npowers2 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[npowers2]

Hi @amitchandak,

Below is a link to an example data set:

Example Data