Help Needed! Divide Column in One Table by Column in Another Table

[jyeager]

Hi there! I'm hoping all the smart people out there can help me with this. 

I need a measure that will divide budgeted_hours by the estimated_units in another table. The caviat is that I have a quantity basis I need referenced in order to do find the right estimated_units. So for example:

Job #1 has budgeted labor hours for the following activities:

• "Set-up" activity: 50 hours
• "Casting" activity: 100 hours
• "Patching" activity: 10 hours

Job #1 also has budgeted quantities that we are producing and each quantity type corresponding to the activity above:

• "Set-ups": 1 (relates to Set-up activity)
• "Beams": 5 (relates to Casting and Patching activities)

I would like to have a measure that would tell me for Job #1 I have 50 hours per set-up (50 hours divided by 1 set-up) and that I have 20 hours per beam for casting (100 hours divided by 5 beams) and 2 hours per beam for patching (10 hours divded by 5 beams).

I have attached the two tables and how the are linked:

it should be something like psi-labor_budgets[budgeted_hours] / psi-qty_budgets[orig_est_units] ... but then filtered or realted to the cost_basis.... I know all the smart folks out there can help me with this one! thanks in advance!!

Jim

[Greg_Deckler · Accepted solution]

@jyeager - You will need to use RELATEDTABLE or you could use LOOKUPVALUE so something like:

Measure = 
  DIVIDE(
    SUM('psi-labor_budgets'[budgeted_hours]),
    SUMX(RELATEDTABLE('psi-qty_budgets'),[orig_est_units]),
    0
  )

[jyeager]

@Greg_Deckler Thank you!!