# DAX formula for calculated field from multiple tables



## lochem (Mar 4, 2015)

hi there everyone,

i am struggling with the following task in PowerPivot/Powerview for excel2013 (see tables below) :

Table A: "Jobs Data" has information on "Production Jobs" - each row has a unique job ID number.
Table B: "Direct Labor Data"  has records with information about "direct labor" hours required for each Job on different days, and how many units were completed by laborers in each time block.
Table C: "Indirect Labor Data" has records on "indirect labor" hours required on different days per Job.

The task is to calculate "Units Per Hour" by dividing units completed/(direct labor+indirect labor) as follows (*in pseudo code - assuming i am creating the calculated field in the Direct Labor table*):


```
Units Per Hour = Direct Labor[Units Completed] / (Direct Labor[direct manhours] + Indirect Labor[Indirect manhours])
```

i have tried a few ways to do this and i am getting either errors or incorrect measures!

anyone know how i can combine these 2 fields from different tables properly??

*Jobs Data: (defined here for relationship purposes between tables B  and C):*


Job IDCustomer IDEtc.Job 1
customer 3Job 2customer 6

<tbody>

</tbody>*
Direct Labor:*

DateJob IDWorkstationStart TimeEnd Time# of ppl*Direct Manhours*_*Units Completed*_3-febJob 1station a...=(end time - start time) * (# of ppl)54 febJob 1station b...234febJob 2station x...125-febJob 2station y...17

<tbody>

</tbody>*
Indirect Labor:
*
DateJob IDIndirect Taskstart timeend time# of ppl*Indirect Manhours*3 febJob 2task a...calculated same as above4 FebJob 1task x...5 febJob 2task y...3 febJob 1task a...

<tbody>

</tbody>


----------



## mneumann (Mar 5, 2015)

I think as long as you have the proper relationships (with both indirect and direct labor referencing back to the jobs data table, and using the jobs data job id column as the row labels on your pivot table) all you are missing is the aggregation of the data related to the jobs on the data tables, so:

sum('Direct Labor'[Units Completed])/(sum('Direct Labor'[Direct Manhours])+sum('Indirect Labor'[Indirect Manhours]))


----------



## lochem (Mar 5, 2015)

mneumann said:


> I think as long as you have the proper relationships (with both indirect and direct labor referencing back to the jobs data table, and using the jobs data job id column as the row labels on your pivot table) all you are missing is the aggregation of the data related to the jobs on the data tables, so:
> 
> sum('Direct Labor'[Units Completed])/(sum('Direct Labor'[Direct Manhours])+sum('Indirect Labor'[Indirect Manhours]))



sigh... oh dear me.  i realized my mistake before even really reading your response.

indeed, the Job ID wasnt being taken from the parent table Job Info.  .... ive changed that and indeed it seems to be OK.

thanks...


----------

