Create a Counted Summary Table

[TBensen]

Hello,

I'm hoping someone can give me a hand with figuring out how to create a summary table of counts on a specific column value from 2 different tables.  Here is what I have going on...

Incident Table: The Incident table contains all of the incidents that are associated to an Employee ID.  This table also contains a Seniority year that is calculated based off of the Incident Date minus the Date of Hire, which gives us a Seniority Year at the time of the incident. 

Employee ID	Record Number	Seniority Year
emp_1	123	2
emp_1	234	2
emp_2	345	4
emp_3	456	7
emp_4	567	5

From this I can get a count of Incidents by Seniority Year:

Employee Table: The employee table contains all of the employees throughout the organization.  This table also contains the Employee ID and Years of Service that is calculated based off of Today minus Date of Hire.

Employee ID	Years of Service
emp_1	3
emp_2	7
emp_3	10
emp_4	7

Using this data set, I can get a count of the organization population based off of the Seniority Years.

Now is where the tricky part begins...or if it even makes sense to do this.

I want to take the Count of Incidents by Seniority and divide it by the Counts of Employees in a Seniority Year bucket to give us a rate of incidents by Seniority Year.  In the picture examples, [Seniority Years 0] 512 / [Years of Serivce 0] 1749 = .293.

I've tried merging tables and creating calculated columns, but for some reason I just can't get it to work out.

Thank You,
Trevor Bensen

[DataZoe · Accepted solution]

Hi @TBensen ,

You can do this by introducing a "Seniority" table with each of the numbers for each row. Then removing any other relationships between the Employee table and Incident table, join them both to the Seniority table on the number of years columns.  

You can then create a measure for Incidents, People, and Rate of Incidents.

Incidents = countrows(Incident)+0

People = COUNTROWS(Employee)+0

Rate of Incidents = DIVIDE([Incidents],[People])

Then you create a table with the Seniority table's Seniority Year and the measures you want.

[TBensen]

Hello,

This worked out great!  Thank you for your help.  I was definitely over-complicating what I was trying to accomplish.

Thanks Again,
Trevor Bensen