Pivot Table with data from two worksheets

Hello all,

I am new to the forums, and fairly new to some of the complex features of Excel. I am currently trying to build a reporting tool for our group that shows the cycle time of our production, and the quality.

The first part, cycle time, I have already built and automated via macros, because I only need 1 sheet of data for the pivot table.

The concern I have is with quality, and I will provide as much detail as possible. Currently, I receive a spreadsheet with production (everything that we produced and sent out) and each has a 7-digit number associated with it. I run production using a count of this 7-digit number. The difficult part is that I receive a second spreadsheet that has a very similar format, but only lists the 7-digit numbers for the transactions that had errors. For example, if I had 10 transactions on my first spreadsheet for production, but only 1 had error, my error spreadsheet I receive would only have 1 transaction on it.

What I need is the ability to have 1 chart that shows 1. A distinct count of "Credit_Lead_Num" from the error spreadsheet, 2. A count of "Credit_Lead_Num" from the production sheet, and 3. A percentage of the 2, displaying the accuracy. I added the error data sheet to my data model, which allowed me to get a distinct count, but when I add "Credit_Lead_Num" from the production sheet, it displays the same number for each month and a dialogue box pops up stating that a relationship needs to be formed. When I try to create the relationship, it will not allow me since both columns do not have unique values, due to the fact that each transaction on the error spreadsheet can have multiple errors.

My questions are as follows:

1. Because they are the same data field, but on 2 separate sheets, is what I am trying to accomplish even possible?

2. If the answer to the above is yes, how do I create the relationship correctly?

3. What additional data do you need to help answer this?

I would like to use slicers for the data so that individuals can filter the data and self-serve for their KPIs. Is a helper column dynamic in that as I change the filters on the pivot, the helper column would change?

Here is an example workbook. The production tab shows 9 production items and the error data shows 2 different transactions; 1 transaction with only 1 DR- error, and 1 transaction with 5 errors of various types.

What I need to show is the number of transactions with DR- errors (2 in this case) against total transactions (9 in this case), and calculate an accuracy. From there, I can add a pivot chart and add slicers in to help each individual employee self-serve for their metrics.

In further review, I found that the Record_Number is a more accurate field to use, so I would like to use that instead of Credit_Lead_Num as I previously stated.