I’m a new powerBI user and I’m having trouble with something I’m really sure is very simple I just can’t twist my brain around to be able to figure it out.
I have 2 data tables, one for salaries and one for time per project. they are linked through an employeeID table.
I’m trying to calculate an hourly rate as total salary / total hours per employee. so that I can further calculate how much their time on each project costs.
However, when I do this because there are multiple time rows for each employee the number I’m getting is too high. If I max/min/average the time then the total in the pivot does the same rather than being a sum.
Can you point me in the right direction please? either with a solution, or by telling me which chapter in the book I’ve misread/misunderstood.
I do not know how your salaries table was constructed, but when I work with salaries they represented what an employee *would* be paid over an entire year (regardless of whether they worked a full year or not). When hourly rates were needed based on salaries, it was calculated at [Salary Amount] / 2080.
Using the above logic, I would add a calculated column to the salaries table for hourly rate. I would also add a calculated column to the time table for Labor Cost that multiplied the RELATED ( Saleries[Hourly Rate] ) * Time[Hours].
A measure would then be Total Labor Cost = SUM ( Time[Labor Cost] ).
I would also consider creating a calculated table for Projects in Power BI and relating it to the time fact table (an easy task in Power BI Desktop).
Your calculations could get more detailed if the salaries table contained hourly employees and then you would have to account for overtime scenarios. If such a case, the time table should indicate whether the hours recorded were for straight time, overtime, holiday time, etc.
My problem is that not everyone is working standard 2080 hours. they are all on a fixed salary but I want the hourly rate to reflect actually how much they are working rather than the standard time.
The salary table is YTD not a full year. So i have Salary YTD and Hours at a project level.
I’ve used filter and earlier to calculate the total time per person but then i can’t use that as the denominator to get a % for each line.
Unfortunately my model is too large to upload and I’m not sure how to break it down.