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, this one is a little complicated. let me see if I can explain it clearly.
I need to calculate the cost of human resources by department. The tricky part is that each employee can point working time to different departments according to its activity of that day, and have different salary each month due to overtime work.
(dim table) EMPLOYEE TABLE
Employee_ID |
0000 |
7777 |
8888 |
9999 |
(fact table) BUDGET TABLE
Employee_ID | Pay Day (dd/mm/yyyy) | VALUE |
0000 | 07/02/2022 | $ 27.106,00 |
0000 | 06/01/2022 | $ 178.917,00 |
0000 | 06/01/2022 | $ 216.838,00 |
0000 | 06/01/2022 | $ 98.939,00 |
0000 | 06/01/2022 | $ 13.160,00 |
0000 | 07/02/2022 | $ 13.160,00 |
0000 | 07/02/2022 | $ 140.953,00 |
7777 | 06/01/2022 | $ 22.560,00 |
7777 | 07/02/2022 | $ 22.560,00 |
8888 | 06/01/2022 | $ 9.400,00 |
8888 | 07/02/2022 | $ 9.400,00 |
9999 | 06/01/2022 | $ 16.920,00 |
9999 | 07/02/2022 | $ 16.920,00 |
9999 | 06/01/2022 | $ 9.000,00 |
9999 | 07/02/2022 | $ 3.000,00 |
(fact table) WORKING TIME TABLE
Employee_ID | Date (dd/mm/yyyy) | Department | Time Pointed |
0000 | 01/01/2022 | AB_1001 | 5,0 |
0000 | 01/01/2022 | AB_1006 | 3,0 |
0000 | 01/02/2022 | AB_1001 | 7,0 |
0000 | 01/02/2022 | AB_1006 | 1,0 |
0000 | 02/02/2022 | AB_1001 | 8,0 |
9999 | 01/01/2022 | AB_1830 | 8,0 |
9999 | 02/01/2022 | AB_1830 | 3,0 |
9999 | 02/01/2022 | AB_2510 | 5,0 |
8888 | 01/01/2022 | AB_3003 | 8,0 |
8888 | 02/02/2022 | AB_3003 | 8,0 |
7777 | 01/01/2022 | AB_2000 | 8,0 |
7777 | 01/02/2022 | AB_2000 | 8,0 |
The two fact table are connect by the dim table, but i'm not sure about the right connections (both or single).
Here are some intermediate (virtually) table that I get, just to make the explanation more clear, but they do not need to be constructed:
JANUARY PROPORTIONAL TIME POINTED
Employee_ID | AB_1001 | AB_1006 | AB_1830 | AB_2510 | AB_3003 | AB_2000 |
0000 | 63% | 38% | ||||
7777 | 100% | |||||
8888 | 69% | 31% | ||||
9999 | 100% |
FEBRUARY PROPORTIONAL TIME POINTED
Employee_ID | AB_1001 | AB_1006 | AB_1830 | AB_2510 | AB_3003 | AB_2000 |
0000 | 94% | 6% | ||||
7777 | 100% | |||||
8888 | ||||||
9999 | 100% |
BUDGET OF EACH DEPARTMENT PER MONTH
MONTH | AB_1001 | AB_1006 | AB_1830 | AB_2510 | AB_3003 | AB_2000 |
January | $ 317.408,75 | $ 190.445,25 | $ 6.462,50 | $ 2.937,50 | $ 25.920,00 | $ 22.560,00 |
February | $ 169.892,81 | $ 11.326,19 | $ 0,00 | $ 0,00 | $ 19.920,00 | $ 22.560,00 |
And what I really need at the end is this last result:
MONTH | AB_1001 | AB_1006 | AB_1830 | AB_2510 | AB_3003 | AB_2000 |
TOTAL | $ 487.301,56 | $ 201.771,44 | $ 6.462,50 | $ 2.937,50 | $ 45.840,00 | $ 45.120,00 |
Solved! Go to Solution.
Hi @Anonymous
Here's a measure which appears to get what you've requested (although I didn't understand how you got to some of the final figures from your example data, so assumed you'd made a mistake - eg 8888 assigned all time to AB_3003 in the source data but not in the tables underneath)
Anyway, here's the model
The measure is
Department Cost =
VAR _EmployeeAllocation =
ADDCOLUMNS(
SUMMARIZE('Working Time', Employee[Employee_ID],'Date'[Month Year]),
"Pct", DIVIDE(
CALCULATE(SUM('Working Time'[Time Pointed])),
CALCULATE(SUM('Working Time'[Time Pointed]), REMOVEFILTERS(), Employee[Employee_ID] = EARLIER(Employee[Employee_ID]), 'Date'[Month Year] = EARLIER('Date'[Month Year]))
)
)
VAR _EmployeePay =
ADDCOLUMNS(
SUMMARIZE('Budget', Employee[Employee_ID],'Date'[Month Year]),
"Pay", CALCULATE(SUM('Budget'[VALUE]))
)
VAR _Combined =
NATURALINNERJOIN(_EmployeeAllocation, _EmployeePay)
RETURN
SUMX(_Combined, [Pay] * [Pct])
giving these figures
One piece of the logic I didn't understand is knowing what time period pay relates to. So, the pay day on 7th Feb is for what period of days worked? My measure above assumes pay in february is for days worked in february, which seems unlikely to me.
Finally, I think tamerj1's on the right lines to say having rate per hour makes this thing a whole lot easier. Presumably it exists somewhere so people get paid the right amount.
Hi @Anonymous
Here's a measure which appears to get what you've requested (although I didn't understand how you got to some of the final figures from your example data, so assumed you'd made a mistake - eg 8888 assigned all time to AB_3003 in the source data but not in the tables underneath)
Anyway, here's the model
The measure is
Department Cost =
VAR _EmployeeAllocation =
ADDCOLUMNS(
SUMMARIZE('Working Time', Employee[Employee_ID],'Date'[Month Year]),
"Pct", DIVIDE(
CALCULATE(SUM('Working Time'[Time Pointed])),
CALCULATE(SUM('Working Time'[Time Pointed]), REMOVEFILTERS(), Employee[Employee_ID] = EARLIER(Employee[Employee_ID]), 'Date'[Month Year] = EARLIER('Date'[Month Year]))
)
)
VAR _EmployeePay =
ADDCOLUMNS(
SUMMARIZE('Budget', Employee[Employee_ID],'Date'[Month Year]),
"Pay", CALCULATE(SUM('Budget'[VALUE]))
)
VAR _Combined =
NATURALINNERJOIN(_EmployeeAllocation, _EmployeePay)
RETURN
SUMX(_Combined, [Pay] * [Pct])
giving these figures
One piece of the logic I didn't understand is knowing what time period pay relates to. So, the pay day on 7th Feb is for what period of days worked? My measure above assumes pay in february is for days worked in february, which seems unlikely to me.
Finally, I think tamerj1's on the right lines to say having rate per hour makes this thing a whole lot easier. Presumably it exists somewhere so people get paid the right amount.
Hi @PaulOlding ,
Thanks a lot again for you help.
I've just tested it and it worked.... I wouldn't made it my self...
The 8888 example, I've made on purpose, to have less then the total amount, so I would be sure that my example is not just summing up.
Good point about the payday... I've made a mistake. The payday should be one month later. Thanks for the note. You did the right consideration. I will try to correct it with power query, changing the date to one month earlier as the month were the budget was generated, instead of when it was payed.
My problem with the rate per hour is that here in Brazil we get paid by month, and not by day or hour, so my numbers are all on a monthly based salary.
I cannot thank you enough.... You saved my life twice.
Cheers from Brazil
Hi @Anonymous
It is matmatically impossible. Let's suppose the employee worked in two departments and some how we were able to aggregate the pay amount then we will have two unknowns in one linear equation which is impossible to solve. You have to have the hourly pay rate per department per imployer probably in a seperate dim table.
Hi... thanks for the reply... I don't see why it's impossible. I have the sum of the amout payed to an employee and have the proportion of the time of each department in which he worked. So you multiply the budget per the percentage pointed... and aggregate by department.
Yes but the rate per time is diffent for each department per employee therefore all the calculated values are simply wrong. You can consider one of your employees as an example and do manual calculation to validate. Computer does not do what is mathematically impossible.
I've made the calculation on the problem formulation post with the results. I'm not getting your point.
Hi:
If you have a file to share I can check it out. Even if you make up the data. You do not want to use bi-directional filters in most cases many to many is not a solid way to produce your model.
Thanks
Hi... thanks for the reply... I don't have any many-to-many relation on my example.
Cheers...
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 |
---|---|
38 | |
26 | |
20 | |
15 | |
8 |
User | Count |
---|---|
69 | |
47 | |
46 | |
20 | |
16 |