Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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]) ) )
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |