Create a caluculation between two tables

I am a NEWBIE to Power BI and DAX. Any help is greatly appreciated!

I have 2 tables (screenshots attached).

Table 1: TKHours

Table2: PPEAmounts

What I am trying to acheive is:

Thank you in advance for any feedback,

Brett

ankitpatira
9 years ago
brettmeyers You need to create relationship between two tables using common field such as Jobnumber in your case. Once relation is there you can simply create calculated column to get value for each row or create a measure to get difference using sum(PPEAmounts[Amount]) / sum(TKHours[Hours]).
Anonymous
9 years ago
Hi brettmeyers,

Ankipatira ‘s point seems well, you could also use LOOKUPVALUE function to create a measure to achieve your requirement.

I find these tables all have a jobnumber column, I use this column as the search column: (Since I can’t find a same jobNumber between two tables, I have modified the jobnumber)

Dax:
Measure = SUM(PPEAmounts[Amount])/LOOKUPVALUE(TKHours[Hours],TKHours[JobNumber],VALUES(PPEAmounts[JobNumber]))

Result: (I added a column to store and display the measure)

Regards,
Xiaoxin Sheng

ankitpatira
Community Champion
9 years ago
brettmeyers You need to create relationship between two tables using common field such as Jobnumber in your case. Once relation is there you can simply create calculated column to get value for each row or create a measure to get difference using sum(PPEAmounts[Amount]) / sum(TKHours[Hours]).
Anonymous
Not applicable
9 years ago
Hi brettmeyers,

Ankipatira ‘s point seems well, you could also use LOOKUPVALUE function to create a measure to achieve your requirement.

I find these tables all have a jobnumber column, I use this column as the search column: (Since I can’t find a same jobNumber between two tables, I have modified the jobnumber)

Dax:
Measure = SUM(PPEAmounts[Amount])/LOOKUPVALUE(TKHours[Hours],TKHours[JobNumber],VALUES(PPEAmounts[JobNumber]))

Result: (I added a column to store and display the measure)

Regards,
Xiaoxin Sheng

Forum Discussion