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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Job ID[/TD]
[TD]Customer ID[/TD]
[TD]Etc.[/TD]
[/TR]
[TR]
[TD]Job 1
[/TD]
[TD]customer 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job 2[/TD]
[TD]customer 6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Direct Labor:
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Job ID[/TD]
[TD]Workstation[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]# of ppl[/TD]
[TD]Direct Manhours[/TD]
[TD]Units Completed[/TD]
[/TR]
[TR]
[TD]3-feb[/TD]
[TD]Job 1[/TD]
[TD]station a[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD]=(end time - start time) * (# of ppl)[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4 feb[/TD]
[TD]Job 1[/TD]
[TD]station b[/TD]
[TD][/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]4feb[/TD]
[TD]Job 2[/TD]
[TD]station x[/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD][/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]5-feb[/TD]
[TD]Job 2[/TD]
[TD]station y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD]17[/TD]
[/TR]
</tbody>[/TABLE]

Indirect Labor:
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Job ID[/TD]
[TD]Indirect Task[/TD]
[TD]start time[/TD]
[TD]end time[/TD]
[TD]# of ppl[/TD]
[TD]Indirect Manhours[/TD]
[/TR]
[TR]
[TD]3 feb[/TD]
[TD]Job 2[/TD]
[TD]task a[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD]calculated same as above[/TD]
[/TR]
[TR]
[TD]4 Feb[/TD]
[TD]Job 1[/TD]
[TD]task x[/TD]
[TD][/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5 feb[/TD]
[TD]Job 2[/TD]
[TD]task y[/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 feb[/TD]
[TD]Job 1[/TD]
[TD]task a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

 

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