Per Capita cal

[Junayet_Rahaman]

Hi, I want to calculate per capita amount for employees. For one employee multiple pay codes are generated for each month.

Per capita calculation is attached below in Excel screenshots. How do I implement this Power BI DAX?

I need hep on this to calculate, please. Thank you.

Sample Data:

Payroll INC	Period	YearMonth	Year	Month	Employee name	Employee ID	Type of emp	Amount	Paycode
1/4/2022	2022/04	202204	2022	4	A	25890	Local	29000	Y78IL
1/4/2022	2022/04	202204	2022	4	A	25890	Local	5900	O09PJ
1/4/2022	2022/04	202204	2022	4	A	25890	Local	5689	PO89U
5/5/2022	2022/05	202205	2022	5	A	25890	Local	29000	U8IOP
5/5/2022	2022/05	202205	2022	5	A	25890	Local	2589	P09NB
8/6/2022	2022/06	202206	2022	6	A	25890	Local	29000	TW76A
8/6/2022	2022/06	202206	2022	6	A	25890	Local	7825	EU76B
2/1/2023	2023/01	202301	2023	1	B	57698	Contract	35000	YR09JT
2/1/2023	2023/01	202301	2023	1	B	57698	Contract	5000	YT64YU
1/2/2023	2023/02	202302	2023	2	B	57698	Contract	35000	AS8795
1/2/2023	2023/02	202302	2023	2	B	57698	Contract	6500	JGH670
1/2/2023	2023/02	202302	2023	2	B	57698	Contract	7815	KJ97GH
2/3/2023	2023/03	202303	2023	3	B	57698	Contract	35000	ST53GH
2/3/2023	2023/03	202303	2023	3	B	57698	Contract	983.3	BV87YU
2/4/2023	2023/04	202304	2023	4	B	57698	Contract	35000	NB84KJ
2/5/2023	2023/05	202305	2023	5	B	57698	Contract	35000	AQ23LP
1/6/2023	2023/06	202306	2023	6	B	57698	Contract	35000	ZX32FR
1/6/2023	2023/06	202306	2023	6	B	57698	Contract	7985.6	AS19FG
2/7/2023	2023/07	202307	2023	7	B	57698	Contract	35000	AB46JY
2/11/2023	2023/11	202311	2023	11	C	78965	Local	41562.5	AH80YU

[Anonymous]

Hi @Junayet_Rahaman ,

I'm sorry I don't quite understand the logic of the Average People column and why Employee C is only eleven months, can you explain this in more detail?

Best Regards,

Neeko Tang

[Junayet_Rahaman]

Hi Neeko Tang,

Employee C is only 11 because in 2023 year 11 months are ongoing month. So employee C is having amount only for 11 (Nov) so the count for this year is 1. It will divide 1/11. If Employee C was present 2 months then it would divided by 2/11. Please let me know if there is still confusion. Thanks for your support.

[Anonymous]

Hi @Junayet_Rahaman ,

We can create a table.

Table 2 = ADDCOLUMNS(CALENDAR(DATE(2022,1,1),DATE(2023,12,31)),"month",MONTH([Date]))

We can create measures.

Measure 2 = 
var _total=IF(MAX('Table'[Year])<YEAR(TODAY()),12,MONTH(TODAY()))
var _table1=SUMMARIZE(ALL('Table'),'Table'[Employee name],'Table'[Month])
var _month=COUNTROWS(FILTER(_table1,'Table'[Employee name]=MAX('Table'[Employee name]) && [Month] in VALUES('Table 2'[month])))
return  DIVIDE(_month,_total,0)

Measure 3 = 
var _a= CALCULATE(SUM('Table'[Amount]),ALL('Table'))
var _b=SUMX(VALUES('Table'[Employee name]),[Measure 2])
return _a * _b

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Junayet_Rahaman]

Hi,

Thanks for your response. Not getting expected results. I have modified my table a little bit. Suppose Employee C worked in 2022 only for 2 months but again hen joined in November 2023.  For 2022 will be 2/12 but in 2023 it will be 1/11.

Here is my updated data. I am using Year in all my charts. Will wait for your response. Thank you.

Payroll INC	Period	YearMonth	Year	Month	Employee name	Employee ID	Type of emp	Amount	Paycode
2022-01-04	2022-04-01	202204	2022	4	A	25890	Local	29000	Y78IL
2022-01-04	2022-04-01	202204	2022	4	A	25890	Local	5900	O09PJ
2022-01-04	2022-04-01	202204	2022	4	A	25890	Local	5689	PO89U
2022-05-05	2022-05-01	202205	2022	5	A	25890	Local	29000	U8IOP
2022-05-05	2022-05-01	202205	2022	5	A	25890	Local	2589	P09NB
2022-08-06	2022-06-01	202206	2022	6	A	25890	Local	29000	TW76A
2022-08-06	2022-06-01	202206	2022	6	A	25890	Local	7825	EU76B
2022-08-06	2022-06-01	202206	2022	6	C	78965	Local	39878	JH87GH
2022-08-06	2022-06-01	202206	2022	6	C	78965	Local	8975	PO134LK
2022-08-07	2022-07-01	202207	2022	7	C	78965	Local	29678	RT09DP
2023-02-01	2023-01-01	202301	2023	1	B	57698	Contract	35000	YR09JT
2023-02-01	2023-01-01	202301	2023	1	B	57698	Contract	5000	YT64YU
2023-01-02	2023-02-01	202302	2023	2	B	57698	Contract	35000	AS8795
2023-01-02	2023-02-01	202302	2023	2	B	57698	Contract	6500	JGH670
2023-01-02	2023-02-01	202302	2023	2	B	57698	Contract	7815	KJ97GH
2023-02-03	2023-03-01	202303	2023	3	B	57698	Contract	35000	ST53GH
2023-02-03	2023-03-01	202303	2023	3	B	57698	Contract	983.3	BV87YU
2023-02-04	2023-04-01	202304	2023	4	B	57698	Contract	35000	NB84KJ
2023-02-05	2023-05-01	202305	2023	5	B	57698	Contract	35000	AQ23LP
2023-01-06	2023-06-01	202306	2023	6	B	57698	Contract	35000	ZX32FR
2023-01-06	2023-06-01	202306	2023	6	B	57698	Contract	7985.6	AS19FG
2023-02-07	2023-07-01	202307	2023	7	B	57698	Contract	35000	AB46JY
2023-02-11	2023-11-01	202311	2023	11	C	78965	Local	41562.5	AH80YU