Creating a Pivot Table from Two Data Sets

[4ever7]

Good day.

I'm working on a report that uses information from two data sets, both of which are tabs in my Excel workbook. The first tab is called Failures and it lists the names of the employee name, the failures they received, and the dates when these were evaluated.

SQS_01.png

The second tab contains the evaluation dates and scores received from those evaluations.

SQS_02.png

The report I'm interested at looking at is the failure percentage of the individual. So say, one person had 4 evaluations for the month and had 2 failures, thus the failure rate is 2/4 or 50%. I tried adding the data from the two tabs to the data model, but when I try to connect the data using the employee names I get a message that the selected columns contain duplicate values.

SQS_03.png

Without having to use formulas, is it possible to use pivot tables for this purpose?

The file is uploaded; names were generated using a name generator.

[ORoos]

Hi 4ever7,
Welcome to the forum.
Please note it is much better to attach a sample file (without confidential info) rather than screenshots. It is much easier for members to help you and test possible solutions on your sample data. Please refer to the yellow banner at the top.

Without the actual data.... maybe try:
When you have setup the pivot table on the top ribbon under PivotTable Tools > Fields, Items & Sets in the dropdown, select Calculate Field. Here you can do simple calculations and insert the new field into your pivot table.

[4ever7]

Hi 4ever7,
Welcome to the forum.
Please note it is much better to attach a sample file (without confidential info) rather than screenshots. It is much easier for members to help you and test possible solutions on your sample data. Please refer to the yellow banner at the top.

Without the actual data.... maybe try:
When you have setup the pivot table on the top ribbon under PivotTable Tools > Fields, Items & Sets in the dropdown, select Calculate Field. Here you can do simple calculations and insert the new field into your pivot table.

Hi there, thanks for replying to my inquiry. I have uploaded the file as requested. The pivot table to be made is in the Summary tab. I have used COUNTIF for now, but it will be very challenging if this report is to be sustained using COUNTIF or COUNTIFS because the report is done monthly, and further features such as count of failure types per month, history of failures per agent, etc, will be provided to the recipients on a monthly basis.

[ORoos]

To link data in Pivot tables you need one list with unique values. So you would need a list with the employee names, then you could link the Evaluations data and the Failures data together....working out a percentage from one to the other will still be a challenge.
How about if you combine the 2 tables. Would this be possible? Keep your Evaluations table and add 2 columns; Pass/Fail, Failure Type.
With all in one table, a Pivot table will be easy and you can sort and group as required, including future grouping by month etc.