DAX formula for calculated field from multiple tables

[lochem]

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 ID	Customer ID	Etc.
Job 1	customer 3
Job 2	customer 6

<tbody>
</tbody>

Direct Labor:

Date	Job ID	Workstation	Start Time	End Time	# of ppl	Direct Manhours	Units Completed
3-feb	Job 1	station a	...			=(end time - start time) * (# of ppl)	5
4 feb	Job 1	station b		...			23
4feb	Job 2	station x			...		12
5-feb	Job 2	station y				...	17

<tbody>
</tbody>

Indirect Labor:

Date	Job ID	Indirect Task	start time	end time	# of ppl	Indirect Manhours
3 feb	Job 2	task a	...			calculated same as above
4 Feb	Job 1	task x		...
5 feb	Job 2	task y			...
3 feb	Job 1	task a				...

<tbody>
</tbody>

 

[mneumann]

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]

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