Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Match 2 columns and lookup value

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

PR1PName1Team1In ProgressApp114/3/2020150
PR2PName2Team1In ProgressApp114/3/2020150
PR5PName5Team1HoldApp124/3/2020100
PR1PName1Team2In ProgressApp214/3/2020175
PR2PName2Team2In ProgressApp224/3/2020100
PR3PName3Team2In ProgressApp214/3/2020150
PR5PName5Team2In ProgressApp234/3/202075
PR6PName6Team2HoldApp224/3/2020150
PR2PName2Team3In ProgressApp314/3/2020100
PR3PName3Team3In ProgressApp324/3/2020150
PR5PName5Team4In ProgressApp414/3/2020300
PR3PName3Team5In ProgressApp514/3/2020150
PR6PName6Team5In ProgressApp514/3/2020145
PR4PName4Team6In ProgressApp614/3/2020170
PR1PName1Team6In ProgressApp614/3/2020180
PR6PName6Team6In ProgressApp614/3/2020190
PR4PName4Team6In ProgressApp624/3/2020140
PR3PName3Team7HoldApp714/3/2020160
PR2PName2Team7In ProgressApp714/3/2020250
PR1PName1Team8In ProgressApp814/3/2020120
PR5PName5Team8In ProgressApp814/3/202080

 

Table : 2

PR_NumberTeamResourceBooking_dateHours

PR1Team1Res14/1/20208
PR2Team1Res14/2/20208
PR1Team1Res14/3/20208
PR2Team1Res14/6/20208
PR1Team1Res24/1/20208
PR2Team1Res24/2/20208
PR1Team1Res24/3/20208
PR2Team1Res24/6/20208
PR6Team2Res214/1/20208
PR1Team2Res214/2/20208
PR2Team2Res214/3/20208
PR3Team2Res214/6/20208
PR1Team2Res224/1/20208
PR4Team2Res224/2/20208
PR1Team2Res224/3/20208
PR2Team2Res224/6/20208
PR2Team3Res314/1/20208
PR2Team3Res314/2/20208
PR2Team3Res314/3/20208
PR2Team3Res314/6/20208
PR3Team3Res324/1/20208
PR3Team3Res324/2/20208
PR3Team3Res324/3/20208
PR3Team3Res324/6/20208
PR5Team4Res414/1/20208
PR5Team4Res414/2/20208
PR5Team4Res414/3/20208
PR5Team4Res414/6/20208
PR5Team4Res424/1/20208
PR5Team4Res424/2/20208
PR5Team4Res424/3/20208
PR5Team4Res424/6/20208
PR3Team5Res514/1/20208

 

 

1 ACCEPTED SOLUTION
vivran22
Community Champion
Community Champion

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:

Capture.JPGSolution 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

 

View solution in original post

2 REPLIES 2
vivran22
Community Champion
Community Champion

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
Community Champion
Community Champion

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:

Capture.JPGSolution 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

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.