Forum Discussion

sanderson82's avatar
sanderson82
Icon for Helper I rankHelper I
7 years ago
Solved

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 empl...
Need Help
  • TeigeGao's avatar
    TeigeGao
    7 years ago

    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's avatar
sanderson82
Icon for Helper I rankHelper I
7 years ago

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

 

copykai's avatar
copykai
Icon for Resolver I rankResolver I
7 years ago

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