Sum with ID and Date from a different table

[PatrickLamoste]

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.

[v-lili6-msft]

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

[PatrickLamoste]

Hi @v-lili6-msft  Lin,

Thank you for your reply. I tried to tweak the formula a bit but still a dead end.

I was wondering if it's possible to summarize Table2 [OT] and will be put into Table1 [RegularHours]. But since Table 1 has duplicate dates I need to only display 7 and not the sum of the whole row when  I put it in a visual. Like this.

Table1
Employee ID	Hour Type	Date	Hours	OT Hours Sum
110290	Regular Hours	24/04/2019	1	7
110290	Regular Hours	24/04/2019	3	7
110290	Regular Hours	24/04/2019	2	7

Table2
Employee ID	Hour Type	Date	Hours
110290	OT Hours	24/04/2019	2
110290	OT Hours	24/04/2019	3
110290	OT Hours	24/04/2019	2

So when I put in a table visual it would look like this:

Visual: Table
Employee ID	Date	Regular Hours	OT Hours
110290	24/04/2019	6	7

[Ashish_Mathur]

Hi,

If your end objective is to show Regular and OT hours in a single Table visual, then you need not bring over OT hours from Table2 into Table1.  If my approach looks suitable to you, then post back and I will share my solution with you.

[v-lili6-msft · Accepted solution]

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

[PatrickLamoste]

ID	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