Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |