Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |