Need help to Calculate the FPY Based on 2 columns with Pass and Fail

[TcT85]

Hi,

From the data table, I need to calculate the first pass yield based on Serial Number.

One serial number has many Result, but I'am only intrested if the serial number has a defect but i want to know the procentage.

In this case I wan't the result to show 50% FPY since serialnumber 2222222 has a defect and serialnumber 44444444 has 0 defects.

How should i write the DAX formulation?

Serial number	Reference	Pin nr	Volume	Result
2222222	C1	1	56	Defect
2222222	C1	2	55	Defect
2222222	C3	1	103	GooD
2222222	C3	2	104	GooD
2222222	C5	1	105	False Call
2222222	C5	2	106	GooD
2222222	C7	1	107	False Call
2222222	C7	2	108	GooD
2222222	C9	1	30	Defect
2222222	C9	2	0	Defect
4444444	C1	1	103	GooD
4444444	C1	2	103	GooD
4444444	C3	1	103	GooD
4444444	C3	2	104	GooD
4444444	C5	1	105	GooD
4444444	C5	2	106	GooD
4444444	C7	1	107	GooD
4444444	C7	2	108	GooD
4444444	C9	1	108	GooD
4444444	C9	2	108	GooD

[tackytechtom]

Hi @TcT85 ,

Could you provide the data and maybe an outcome exactly of what you would like to achieve?

I feel like I would only be guessing right now 🙂

Thanks!

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[TcT85]

Hi Tom,

Sorry for lack of information.

I would like to recieve the Firstpass yield based on a formula with Serialnumber and Result.

Total FPY for all products

50%

and

Product	FPY
A	50%
B	100%
C	0%

Here is the set of data:

Product	Serialnumber	Reference	Pin Nr	Volume	Result
A	1111	C1	1	56	Defect
A	1111	C1	2	55	Defect
A	1111	C2	1	103	Good
A	1111	C2	2	104	Good
A	2222	C1	1	105	Good
A	2222	C1	2	106	Good
A	2222	C2	1	107	Good
A	2222	C2	2	108	Good
B	3333	C1	1	102	Good
B	3333	C1	2	103	Good
B	3333	C2	1	101	Good
B	3333	C2	2	102	Good
C	4444	C1	1	30	Defect
C	4444	C1	2	0	Defect
C	4444	C2	1	103	Good
C	4444	C2	2	102	Good

But with your formula I get this result.

The product C need to show 0% and product B need to show 100%

[tackytechtom · Accepted solution]

Hi @TcT85 ,

Thanks for your reply and clarification.

How about this:

Here comes the measure:

TomsFPYMeasure = 
VAR _helpTable =
SUMMARIZE ( 
    'Table',
    'Table'[Serialnumber],
    "SerialNumberCount", DISTINCTCOUNT ( 'Table'[Serialnumber] ),
    "DefectYesNo", IF ( CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Result] = "Defect" ) > 0, 0, 1 )
)
RETURN
DIVIDE ( SUMX ( _helpTable, [DefectYesNo] ) ,SUMX ( _helpTable, [SerialNumberCount] ) )

Does this work for you? 🙂

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[TcT85]

Hi Tomfox,

This is working perfectly, I got excatly what I was looking for. 😀

But can you please explain the red marked area, how this works?

Just in case i would copy this formula for some other cases.

I appreciate your time, patience and expertize, big thanks! 😀

[tackytechtom]

Hi @TcT85 ,

Awesome that I could help you! 🙂

The marked part meets the statements of the if clause: The logical test condition, the result to be returned if the logical test is TRUE (in our case: 0) and the result to be returned if the logical test is FALSE (in our case: 1)

The logical test checks whether there is a "defect" row for each serial number. If there is one, then we return 0, otherwise 1. This is how the helpTable looks like:

Later, we could just use these columns to divide over the sum of DefectYesNo by the sum of SerialNumberCount .

Have a great day! 🙂

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[tackytechtom]

Hi @TcT85 ,

You can try this and let me know whether it works for you:

TomsFPYMeasure = 
VAR _helpTable =
SUMMARIZE ( 
    Table,
    Table[Serial number],
    "SerialNumberCount", DISTINCTCOUNT ( Table[Serial number] ),
    "DefectYesNo", CALCULATE ( COUNTROWS ( Table ), Table[Result] = "Defect" ) 
)
RETURN
DIVIDE ( COUNTX ( _helpTable, [DefectYesNo] ), SUMX ( _helpTable, [SerialNumberCount] ) )

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[TcT85]

Hi Tom,

Thanks for the formula, i got out 50% that I wanted.

But i encountered another issue with this formula when i added som more data.

Product C , i want that product to show 0% FPY instead of 100%.