Lookup with dynamic search value

[Seb5000]

Hi, I'm pretty new to Power BI, coming from pretty advanced Excel but not too familiar with Power Query and DAX.

I have an employee table with a list of unique names and their salary. I also have a projects table with a list of projects, the names of employees working on each project and the number of hours they worked on each project. I would like to allocate their salary cost back to each project in Power BI.                 

Employee Data:               

Name	Salary
John	$ 5 000
Paul	$ 6 000
Sue	$ 4 000
Jane	$ 7 000

Project Data:

Project	Name	Hours
Alpha	John	80
Alpha	Paul	40
Foxtrot	Paul	40
Foxtrot	Sue	160
Tango	Jane	20
Tango	Paul	80
Tango	John	80
Zulu	Jane	140

In Excel, I would do a SUMIF on the Employee Data table to get a total number of hours worked then calculate a rate by dividing their salary by the total number of hours. On the Project Data table, I would then multiply the hours worked on each project by a VLOOKUP of the rate by name.

I am able to calculate the rate in Power BI, by cannot figure out how to lookup and multiply that rate to get to a cost per employee per project. I've tried the LOOKUPVALUE function but can't figure out how use it such that it calculates for each row (ie, for looks up the rate for each employee).

Any assistance would be greatly appreciated!

[Phil_Seamark · Accepted solution]

HI @Seb5000

I added this Calculated Measure to my Employee Table and it seems to calculate what I think you are after.

It does rely on a one to many relationship between EmployeeData and ProjectData

Cost Per Employee = 
		SUMX(
			'EmployeeData',
			'EmployeeData'[Salary] * CALCULATE(SUM(ProjectData[Hours])
				)
			)

[Seb5000]

Hi, I'm pretty new to Power BI, coming from pretty advanced Excel but not too familiar with Power Query and DAX.

I have  an employee table with a list of unique names and their salary. I also have a projects table with a list of projects, the names of employees working on each project and the number of hours they worked on each project. I would like to allocate their salary cost back to each project in Power BI. 

Employee Data:

Name	Salary
John	$ 5 000
Paul	$ 6 000
Sue	$ 4 000
Jane	$ 7 000

Project Data:

Project	Name	Hours
Alpha	John	80
Alpha	Paul	40
Foxtrot	Paul	40
Foxtrot	Sue	160
Tango	Jane	20
Tango	Paul	80
Tango	John	80
Zulu	Jane	140

In Excel, I would do a SUMIF on the Employee Data table to get a total number of hours worked then calculate a rate by dividing their salary by the total number of hours. On the Project Data table, I would then multiply the hours worked on each project by a VLOOKUP of the rate by name. 

I am able to calculate the rate in Power BI, by cannot figure out how to lookup and multiply that rate to get to a cost per employee per project. I've tried the LOOKUPVALUE function but can't figure out how use it such that it calculates for each line.

Any assistance would be greatly appreciated!