Calculating on two different tables

[Anonymous]

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

[v-zhenbw-msft]

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.

[Anonymous]

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 xx

Month 2 = Department A is ...

[v-zhenbw-msft · Accepted solution]

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.

[amitchandak]

@Anonymous , Not very clear. Create dimension of department and date and you can this formula working

divide(Table2[workhourmonth]),sum(Table1[RatedHoursPrEmployee]))