Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create sickness rate using columns from two tables

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

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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))

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

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