Calculation with columns in different tables

[sanderson82]

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

[ZunzunUOC]

Hi @sanderson82 , Could you show the data?

[sanderson82]

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

[TeigeGao · Accepted solution]

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

[sanderson82]

Thanks @TeigeGao that is exactly what I was after.  I just have one issue now, do you know how I show the cost as a sum?

[copykai]

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