Create sickness rate using columns from two tables

[Anonymous]

I need to calculate sickness rate that can be grouped by department, month/year and filtered by sickness type.

I have 2 tables

1. Headcount - it has staff detail for each month e.g ID =11 appears twice

2. Time off data - each day off is a row with relevant department and reason

Final Result should look as below. FINAL RESULTTIME OFFHEADCOUNT

I have been able to create new IDs in both table as ID-MonthYear and create a many to one relationship with TimeOff and Headcount data. With this i calculated rate as sum(Time off data [time off]/(sum(Headcount[fte])*21.  But when I attempt to create Final Result table as above, the rates are not accurate. Please help

Note: (sum(Headcount[fte])*21 calculates available days. As sickness rate is timeoff divided by avaliable days

[amitchandak · Accepted solution]

@Anonymous , I am assuming you can common Month/Date and department dimension.

The this measure should work

divide(sum(Time off data [time off]),(sum(Headcount[fte])*21))

[Anonymous]

Do you mean instead of creating and using ID-yearMonth as common link for the relationship, I create a dept-yearMonth column and  use this as a common link for the relationship?

[Anonymous]

This worked - creating and using dept-month/year as the new ID 
Thank you @amitchandak