Forum Discussion
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 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!
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]) ) )
2 Replies
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!
