Create a Counted Summary Table

Question

Hello,&nbsp;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.&nbsp; Here is what I have going on...&nbsp;Incident Table: The Incident table contains all of the incidents that are associated to an Employee ID.&nbsp; 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.&nbsp;&nbsp;Employee IDRecord NumberSeniority Yearemp_11232emp_12342emp_23454emp_34567emp_45675&nbsp;From this I can get a count of Incidents by Seniority Year:&nbsp;Employee Table: The employee table contains all of the employees throughout the organization.&nbsp; This table also contains the Employee ID and Years of Service that is calculated based off of Today minus Date of Hire.&nbsp;Employee IDYears of Serviceemp_13emp_27emp_310emp_47&nbsp;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.&nbsp; 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.&nbsp;Thank You,Trevor Bensen&nbsp;

datazoe · Accepted Answer

Hi&nbsp;TBensen&nbsp;,
&nbsp;
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.&nbsp;&nbsp;

&nbsp;
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])
&nbsp;
Then you create a table with the Seniority table's Seniority Year and the measures you want.

&nbsp;

Forum Discussion

Create a Counted Summary Table

2 Replies

Related content

Power BI July 2026 Feature Summary

Create count measure

Schedule an email of AI created summary of a semantic model

Creating a custom weekly summary

Counting nulls

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