Forum Discussion

Seb5000's avatar
Seb5000
New Member
9 years ago
Solved

Lookup with dynamic search value

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...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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])
				)
			)

     

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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])
				)
			)