Forum Discussion
Calculating on two different tables
Hi,
I want to calculate how many rated employees we have for department A in January.
The calculation should be total working hours in table 2 = 60 / RatedHoursPrEmployee = 50 = 1,2
I have two tables related by a 1-many using an unique PK (Month+Department) on Table 1 and (Month+Department) on table 2 :
Table 1: Table with rated hours pr employee pr department
| Month | Department | RatedHoursPrEmployee |
| Jan | Department A | 50 |
| Jan | Department B | 40 |
| Feb | Department A | 50 |
| Feb | Department B | 40 |
Table 2: Table with transactiondata on working hours pr day pr employee
| Date | Name | Department | WorkingHours | Month |
| 1. Jan | John | Department A | 20 | Jan |
| 2. Jan | John | Department A | 20 | Jan |
| 10. Jan | John | Department A | 20 | Jan |
It is possible for me to restructure table 1 as it is manually made.
Thanks!
Mikkel
Hi Anonymous ,
We can create a calculate column and a measure to meet your requirement.
1. Create the related column in Sickness table.
Related = 'Sickness'[Month] &"-"& 'Sickness'[Department]2. Then create a relationship between Table 1 and Sickness table.
3. At last we can create a measure to get the result.
Measure 2 = DIVIDE(CALCULATE(SUM(Sickness[SicknessHours])) / 7.4,[Measure])In you sample data, department A is 9 hours in month 1, so the result we get is 1.01, not the 1.97.
If you have any question, please kindly ask here and we will try to resolve it.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
4 Replies
Anonymous , Not very clear. Create dimension of department and date and you can this formula working
divide(Table2[workhourmonth]),sum(Table1[RatedHoursPrEmployee]))
Hi Anonymous ,
In your Table 1, is there no date column, just a month column?
If yes, you can refer the following steps.
1. Create related columns in two tables.
Related = 'Table 1'[Month] &"-"& 'Table 1'[Department]Related = 'Table 2'[Month] &"-"& 'Table 2'[Department]2. Then create a relationship based on the Related column.
3. At last we can create a measure and the result like this,
Measure = var _x = DIVIDE(SUM('Table 2'[WorkingHours]),SUM('Table 1'[RatedHoursPrEmployee])) return IF(ISBLANK(_x),"null",_x)If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
HI v-zhenbw-msft
Yes only a month column. I already had the related columns and that part worked fine.
The caclulated measure as well give me the desired result - thanks!My next step is including a table 3 containing sickness to calculate sickness pr "measure".
Sickness table
Month Department SicknessHours 1 Department A 4 1 Department A 5 2 Department A 8 2 Department B 5
The result I am looking for is 1,97
By calculating total number of sick hours pr month pr department. So for department A is 17 hours sickhours in month 1. Then dividing 17 by 7,4 (hours pr day of work) = 2,3 days of sickness pr employee pr month.Finally calculating hours of sickness with ratedworkinghours pr employee.
2,3 / 1,2 (from previous measure) = 1,97
My final result should be
Month 1 = Department A is 1,97
Month 1 = Department B is xxMonth 2 = Department A is ...
Hi Anonymous ,
We can create a calculate column and a measure to meet your requirement.
1. Create the related column in Sickness table.
Related = 'Sickness'[Month] &"-"& 'Sickness'[Department]2. Then create a relationship between Table 1 and Sickness table.
3. At last we can create a measure to get the result.
Measure 2 = DIVIDE(CALCULATE(SUM(Sickness[SicknessHours])) / 7.4,[Measure])In you sample data, department A is 9 hours in month 1, so the result we get is 1.01, not the 1.97.
If you have any question, please kindly ask here and we will try to resolve it.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
