Proportional HR cost per department

[Anonymous]

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

[PaulOlding · Accepted 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.

[Anonymous]

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

[tamerj1]

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. 

[Anonymous]

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.

[tamerj1]

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. 

[Anonymous]

I've made the calculation on the problem formulation post with the results. I'm not getting your point.

[Whitewater100]

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

[Anonymous]

Hi... thanks for the reply... I don't have any many-to-many relation on my example.

Cheers...