Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sanderson82
Helper I
Helper I

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

1 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

View solution in original post

5 REPLIES 5
ZunzunUOC
Resolver III
Resolver III

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 ,

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

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?

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.