Create a caluculation between two tables

[brettmeyers]

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:

• PPEAmounts[Amount] / TKHours[Hours]

Thank you in advance for any feedback,

Brett

[ankitpatira · Accepted solution]

@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]).

[v-shex-msft · Accepted solution]

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 · Accepted solution]

@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]).