Match 2 columns and lookup value

[Anonymous]

Hi,

I've 2 tables, 1st with list of Projects currently tested (Multiple projects & Multiple teams)  

2nd with hours each resource spent from those teams on the projects.

I want to calculate the actuals per Project & Team combination by looking up 2nd table

Current Project List  

Project_NumberProject_nameTeamStatusApplicationRel_Start_dateEstimate

PR1	PName1	Team1	In Progress	App11	4/3/2020	150
PR2	PName2	Team1	In Progress	App11	4/3/2020	150
PR5	PName5	Team1	Hold	App12	4/3/2020	100
PR1	PName1	Team2	In Progress	App21	4/3/2020	175
PR2	PName2	Team2	In Progress	App22	4/3/2020	100
PR3	PName3	Team2	In Progress	App21	4/3/2020	150
PR5	PName5	Team2	In Progress	App23	4/3/2020	75
PR6	PName6	Team2	Hold	App22	4/3/2020	150
PR2	PName2	Team3	In Progress	App31	4/3/2020	100
PR3	PName3	Team3	In Progress	App32	4/3/2020	150
PR5	PName5	Team4	In Progress	App41	4/3/2020	300
PR3	PName3	Team5	In Progress	App51	4/3/2020	150
PR6	PName6	Team5	In Progress	App51	4/3/2020	145
PR4	PName4	Team6	In Progress	App61	4/3/2020	170
PR1	PName1	Team6	In Progress	App61	4/3/2020	180
PR6	PName6	Team6	In Progress	App61	4/3/2020	190
PR4	PName4	Team6	In Progress	App62	4/3/2020	140
PR3	PName3	Team7	Hold	App71	4/3/2020	160
PR2	PName2	Team7	In Progress	App71	4/3/2020	250
PR1	PName1	Team8	In Progress	App81	4/3/2020	120
PR5	PName5	Team8	In Progress	App81	4/3/2020	80

Table : 2

PR_NumberTeamResourceBooking_dateHours

PR1	Team1	Res1	4/1/2020	8
PR2	Team1	Res1	4/2/2020	8
PR1	Team1	Res1	4/3/2020	8
PR2	Team1	Res1	4/6/2020	8
PR1	Team1	Res2	4/1/2020	8
PR2	Team1	Res2	4/2/2020	8
PR1	Team1	Res2	4/3/2020	8
PR2	Team1	Res2	4/6/2020	8
PR6	Team2	Res21	4/1/2020	8
PR1	Team2	Res21	4/2/2020	8
PR2	Team2	Res21	4/3/2020	8
PR3	Team2	Res21	4/6/2020	8
PR1	Team2	Res22	4/1/2020	8
PR4	Team2	Res22	4/2/2020	8
PR1	Team2	Res22	4/3/2020	8
PR2	Team2	Res22	4/6/2020	8
PR2	Team3	Res31	4/1/2020	8
PR2	Team3	Res31	4/2/2020	8
PR2	Team3	Res31	4/3/2020	8
PR2	Team3	Res31	4/6/2020	8
PR3	Team3	Res32	4/1/2020	8
PR3	Team3	Res32	4/2/2020	8
PR3	Team3	Res32	4/3/2020	8
PR3	Team3	Res32	4/6/2020	8
PR5	Team4	Res41	4/1/2020	8
PR5	Team4	Res41	4/2/2020	8
PR5	Team4	Res41	4/3/2020	8
PR5	Team4	Res41	4/6/2020	8
PR5	Team4	Res42	4/1/2020	8
PR5	Team4	Res42	4/2/2020	8
PR5	Team4	Res42	4/3/2020	8
PR5	Team4	Res42	4/6/2020	8
PR3	Team5	Res51	4/1/2020	8

[vivran22]

Hello @Anonymous ,

Did it solve your issue? If yes, then please mark it as a solution as it will help others in the community.

If not, then please share more details.

Cheers!
Vivek

https://www.vivran.in/
Connect on LinkedIn

[vivran22 · Accepted solution]

Hello @Anonymous ,

You may try this:

Estimate Hours = SUM(table1[Estimate])

Actual Hours = SUM(table2[Hours])

Total Hours Team = 
CALCULATE(
    [Actual Hours],
    TREATAS(VALUES(table1[Team]),table2[Team]),
    TREATAS(VALUES(table1[Project_Number]),table2[PR_Number])
)

Output:

Solution pbix file here

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn