I'm currently working on a dataset to analyze training completion records of users. A training/course can be a part of one or multiple curriculums. But I don't want to consider duplicate records generated because of the curriculum field. It is possible that the same training was assigned multiple times and I want to count those assignments. Following are the sample records from the dataset
|Employee Name||Department||Training Assigned Date||Training Completion Date||Training Status||Training Title||Curriculum|
|John Doe||Customer Care||11/20/2018||12/15/2018||Completed||Effective Communication||Communications|
|John Doe||Customer Care||11/20/2018||12/15/2018||Completed||Effective Communication||Advance Communications|
|John Doe||Customer Care||11/20/2018||12/15/2018||Completed||Effective Communication||Communications for Services Department|
|John Doe||Customer Care||2/1/2019||Null||In Progress||Effective Communication||Advance Communications|
|Mark Robin||Customer Care||11/20/2018||12/23/2018||Completed||Email Writing||Communications|
|Mark Robin||Customer Care||11/20/2018||12/23/2018||Completed||Email Writing||Advance Communications|
|Mark Robin||Customer Care||11/20/2018||12/28/2018||Completed||Effective Communication||Communications|
|Mark Robin||Customer Care||11/20/2018||12/28/2018||Completed||Effective Communication||Advance Communications|
|Mark Robin||Customer Care||11/20/2018||12/28/2018||Completed||Effective Communication||Communications for Services Department|
In the above table, Joh Doe completed "Effective Communication" training once. But, because the training is a part of 3 curriculums we get here 3 completion records for the same training. Calculating total trainings on John Doe's transcript should be = 2 and trainings on Mark's transcript should be = 2. And, total assignments in "Customer Care" department should be 4. When I omit the Curriculum field from dataset and select only unique records based on the other 6 fields. Then I get the desired result. However, if I keep the Curriculum field then I get inflated numbers. e.g. total trainings on John Doe's transcript = 4 and trainings on Mark's transcript = 5. I want to design dashboard in such way that top worksheet shows total trainings which do not consider Curriculum and bottom view should give tabular details of each training which will look like the table mentioned above and should display all the curriculums. Please advise how to proceed with this issue.
Thanks in advance.