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.

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

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.

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

 

[sandy666]

try Alt+D+P
or
Power Query

 

[4ever7]

try Alt+D+P
or
Power Query

Hey Sandy! I have the file uploaded here: SQS Sample.xlsx

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.

 

[sandy666]

see this one

Name	Failures	Evaluations	Failure%		Name	Failures	Evaluations	Failure %
Clarinda Hinderliter	1	5	20%		Bernadine Primo	1	5	20%
Ginette Quin	3	5	60%		Cayla Mullinax	1	5	20%
Harrison Sine	1	5	20%		Clarinda Hinderliter	1	5	20%
Cayla Mullinax	1	5	20%		Dagmar Ristau	1	5	20%
Maire Laramie	2	5	40%		Geneva Tant	1	5	20%
Bernadine Primo	1	5	20%		Ginette Quin	3	5	60%
Geneva Tant	1	5	20%		Hannelore Letcher	0	5	0%
Dagmar Ristau	1	5	20%		Harrison Sine	1	5	20%
Hannelore Letcher	0	5	0%		Inez Bridgers	2	5	40%
Noma Austin	0	5	0%		Laci Hollis	1	5	20%
Lucas Cypher	0	5	0%		Lucas Cypher	0	5	0%
Neomi Loud	0	5	0%		Maire Laramie	2	5	40%
Laci Hollis	1	5	20%		Myong Tellez	1	5	20%
Myong Tellez	1	5	20%		Neomi Loud	0	5	0%
Inez Bridgers	2	5	40%		Noma Austin	0	5	0%

s666-PQ-PT-SQS Sample.xlsx

Microsoft Excel Workbook

1drv.ms

I think Power Query will be enough