Forum Discussion
Helper ISum with ID and Date from a different table
Hi,
I am currently working on 2 tables. The 1st table has the regular hours and the 2nd one has the OT hours.
What I am trying to achieve is to have the OT hours in a new column in the Regular Hours table. I already tried LOOKUPVALUE but it gave me an error.
Attached is the xlsx file.
Thank you in advance.
hi, PatrickLamoste
For your case, you need to create a new table by this formula:
Table = SUMMARIZE ( Table1, Table1[Employee ID], Table1[Date], "Regular Hours", CALCULATE ( SUM ( Table1[Hours] ) ), "OT Hours", CALCULATE ( SUM ( Table2[Hours] ), FILTER ( Table2, Table2[Employee ID] = Table1[Employee ID] && Table2[Date] = Table1[Date] ) ) )Result:
and here is sample pbix file, please try it.
Regards,
Lin
7 Replies
PatrickLamosteID Date Regular Hours 1 01/04/2019 7.00 1 02/04/2019 7.00 2 04/04/2019 7.15 2 05/04/2019 6.85 3 08/04/2019 8.00 3 09/04/2019 7.00 3 10/04/2019 8.50 3 11/04/2019 8.50 4 12/04/2019 3.00 4 15/04/2019 7.00 4 16/04/2019 4.50 5 17/04/2019 8.00 5 18/04/2019 7.50 5 21/04/2019 4.50 5 23/04/2019 9.50 6 24/04/2019 9.00 6 25/04/2019 9.50 6 30/04/2019 7.00 ID Date OT Hours 1 01/04/2019 2.00 2 04/04/2019 4.00 2 05/04/2019 3.00 3 08/04/2019 2.00 3 09/04/2019 2.00 3 10/04/2019 4.00 3 11/04/2019 3.00 4 12/04/2019 2.00 4 16/04/2019 1.00 5 18/04/2019 1.50 5 21/04/2019 2.50 5 23/04/2019 3.50 6 24/04/2019 4.00 6 25/04/2019 3.00 
v-lili6-msftCommunity Support
hi, PatrickLamoste
You could try these two formulas as below:
Column = LOOKUPVALUE ( Table2[OT Hours], Table2[ID], Table1[ID], Table2[Date], Table1[Date] )Column 2 = CALCULATE ( SUM ( Table2[OT Hours] ), FILTER ( Table2, Table2[ID] = Table1[ID] && Table2[Date] = Table1[Date] ) )Result:
and here is sample pbix file, please try it.
Best Regards,
Lin
- PatrickLamoste
Hello Lin v-lili6-msft ,
I tried the LOOKUPVALUE but it does not work for me it gave this error. It seems that there are some entries with repeated values (EID and Date). Below is my actual list. I also tried the 2nd formula and it gave incorrect values.
Thank you so much for your help.
Table 1 Employee ID Hour Type Date Hours 110290 Regular Hours 01/04/2019 10.00 110290 Regular Hours 02/04/2019 7.00 110290 Regular Hours 03/04/2019 8.00 110290 Regular Hours 04/04/2019 3.00 110290 Regular Hours 04/04/2019 6.00 110290 Regular Hours 05/04/2019 3.00 110290 Regular Hours 05/04/2019 3.00 110290 Regular Hours 08/04/2019 12.00 110290 Regular Hours 09/04/2019 11.00 110290 Regular Hours 10/04/2019 0.00 110290 Regular Hours 10/04/2019 10.00 110290 Regular Hours 11/04/2019 7.00 110290 Regular Hours 11/04/2019 0.00 110290 Regular Hours 12/04/2019 10.00 110290 Regular Hours 15/04/2019 2.00 110290 Regular Hours 15/04/2019 4.00 110290 Regular Hours 15/04/2019 4.00 110290 Regular Hours 16/04/2019 2.00 110290 Regular Hours 16/04/2019 5.00 110290 Regular Hours 17/04/2019 2.00 110290 Regular Hours 17/04/2019 4.00 110290 Regular Hours 17/04/2019 2.00 110290 Regular Hours 18/04/2019 1.00 110290 Regular Hours 18/04/2019 1.00 110290 Regular Hours 18/04/2019 7.00 110290 Regular Hours 22/04/2019 5.00 110290 Regular Hours 23/04/2019 2.00 110290 Regular Hours 23/04/2019 3.00 110290 Regular Hours 23/04/2019 3.00 110290 Regular Hours 24/04/2019 1.00 110290 Regular Hours 24/04/2019 3.00 110290 Regular Hours 24/04/2019 2.00 110290 Regular Hours 25/04/2019 2.00 110290 Regular Hours 25/04/2019 2.00 110290 Regular Hours 25/04/2019 5.00 110290 Regular Hours 26/04/2019 1.00 110290 Regular Hours 26/04/2019 4.00 110290 Regular Hours 26/04/2019 3.00 110290 Regular Hours 29/04/2019 3.00 110290 Regular Hours 30/04/2019 1.00 Table 2 Employee ID Hour Type Date Hours 110290 OT Hours 01/04/2019 10.00 110290 OT Hours 02/04/2019 7.00 110290 OT Hours 03/04/2019 8.00 110290 OT Hours 04/04/2019 9.00 110290 OT Hours 05/04/2019 6.00 110290 OT Hours 08/04/2019 12.00 110290 OT Hours 09/04/2019 11.00 110290 OT Hours 10/04/2019 7.00 110290 OT Hours 15/04/2019 10.00 110290 OT Hours 16/04/2019 7.00 110290 OT Hours 17/04/2019 8.00 110290 OT Hours 18/04/2019 9.00 110290 OT Hours 23/04/2019 8.00 110290 OT Hours 24/04/2019 2.00 110290 OT Hours 24/04/2019 3.00 110290 OT Hours 24/04/2019 2.00 110290 OT Hours 25/04/2019 7.00 110290 OT Hours 29/04/2019 10.00 110290 OT Hours 30/04/2019 8.00 - v-lili6-msft
hi, PatrickLamoste
Yes, the problem is that there are some entries with repeated values (EID and Date), So you could not use LOOKUPVALUE in this case.
For your requirement, you have to aggregate [Hours] value by SUM/MIN/MAX as 2nd formula, otherwise, what is the expected output for [Employee ID] 110290 and [Date] 24/04/2019?
Regards,
Lin