topic Re: Calculating yield for production data in DAX Commands and Tips

Calculating yield for production data

Anonymous — Thu, 06 Feb 2020 11:39:26 GMT

Hi all,

I'm trying to calculate % yield for some test rig data. Each product, identified by a unique serial number goes through the test and gets a pass of fail result. What I want to do is use the latest test date for each serial number (as each serial number may have gone through multiple tests) and count the amount of pass results. I then plan to divide this number of records by the total number of unique serial numbers in order to get my % yield figure.

Here is a sample of the data:

VIDATE	VITIME	SERIAL_NUMBER	TEST_OUTCOME
17/07/18	15:27:38	670W16	PASS
17/07/18	15:38:29	670W16	FAIL
17/07/18	15:40:20	670W16	PASS
17/07/18	15:41:34	670W16	PASS
14/09/17	11:56:07	5087W3	PASS
21/09/17	9:23:53	5062H6	PASS
14/09/17	11:54:07	5057H8	PASS
05/07/17	7:59:39	357R35	FAIL
11/07/17	12:31:16	357R35	FAIL
02/03/17	10:24:00	357R35	PASS

Re: Calculating yield for production data

Anonymous — Thu, 06 Feb 2020 13:18:09 GMT

First calculate total count.

Total_count=calculate(Count(table[serial_number]),Allexcept(table,serial_number))

It will return total count for each serial number.

Then check how many serial number have pass status.

Total_count_Pass=calculate(Count(table[serial_number]),Allexcept(table,serial_number)),filter(table,table[testoutcome]="Pass").

Pass percentage=Divide(Total_count,Total_count_Pass,0)

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Re: Calculating yield for production data

Anonymous — Thu, 06 Feb 2020 13:30:27 GMT

Hi Anonymous ,

Thank you for your help! However this is returning a result of 100% and I'm expecting it to be just under that. How do I bring in the date/time element? I need to count the Pass results only for the serial numbers that have passed on the latest test.

Hope that makes sense.

Many thanks

Re: Calculating yield for production data

Anonymous — Thu, 06 Feb 2020 13:40:49 GMT

VIDATE	VITIME	SERIAL_NUMBER	TEST_OUTCOME
17/07/18	15:27:38	670W16	PASS
17/07/18	15:38:29	670W16	FAIL
17/07/18	15:40:20	670W16	PASS
17/07/18	15:41:34	670W16	PASS
14/09/17	11:56:07	5087W3	PASS
21/09/17	9:23:53	5062H6	PASS
14/09/17	11:54:07	5057H8	PASS
05/07/17	7:59:39	357R35	FAIL
11/07/17	12:31:16	357R35	FAIL
02/03/17	10:24:00	357R35	PASS

So you need those bold one with pass status??

Flag=

var Latest_Status_time = CALCULATE(Max(Sheet5[VITIME]),ALLEXCEPT(Sheet5,Sheet5[VIDate]),filter(Sheet5,Sheet5[Status]="Pass")

Return

Sumx(Sheet5,if(Sheet5[VITIME]=Latest_Status_time,1,0)

it will return 1 for latest time with pass status.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Re: Calculating yield for production data

Anonymous — Thu, 28 May 2020 22:02:04 GMT

I need to do the exact same, but summarize it in a graph showing Yield by Month.

Re: Calculating yield for production data

Anonymous — Sun, 31 May 2020 17:08:26 GMT

Your requirements are not clear. Please give us an example of your calculations on an example table. I've tried several different scenarios and the yield always brings figures that are no less than 100%. You have to clearly explain what you want.

Best
D