Forum Discussion
Lookup with dynamic search value
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]) ) )
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!