Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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]).
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
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
@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]).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
82 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |