Forum Discussion
Calculation with columns in different tables
Hi
Quite new to Power BI. I've created a timesheet app in PowerApps and the data is stored on a sharepoint list. Name, Date, Contract, Hours. I also have a second sharepoint list holding the employee names and their hourly rate.
In Power BI I want to show as cost calculation which would be Timesheet[Hours] * Employee[Rate] so I can see the labour cost for each contract however I am struggling to achieve this.
I have a bi-directional Many to 1 join using the Name field in both tables.
Any help much appreciated
Regards
Steven
Hi sanderson82 ,
Firstly, we can connect to the data using the Sharepoint List connector, then import these two tables to PowerBI, then create a many to one relationship between these tables.
After that, we can create a measure:
CALCULATE (MIN('Timesheets'[Hours]) * MIN('Employees'[Rate]))This measure will be displayed in the table Timesheet.
Best Regards,
Teige
5 Replies
Hi sanderson82 , Could you show the data?
Hi ZunzunUOC
Please see below a summary of the 2 lists
Timesheets
Name - Single line of text
Date - Date and Time
Contract - Single line of text
Task - Single line of text
Hours - Number
Supervisor - Single line of text
Employees
Name - Single line of text
Role - Choice
Supervisor - Choice
Status - Choice
Rate - Currency
All the reporting will be done from the Timesheet list. My plan was to create a link between the names in the 2 lists, so I could then calculate a cost based on the hours recorded in the timesheet list against that employees hourly rate.
There would be multiple entries on a daily basis to the timesheet list. I want to then produce weekly reporting which shows labour costs filtered by Contract, Task etc
Hope that makes sense
Hi Sanderson82,
What is the formula for your measure?
To ensure that the rowlevel context is kept you should use the CALCULATE function:Labour Cost := CALCULATE(Timesheet[Hours] * Employee[Rate])
Should be something like this.
Best Regards
Kaj
