Per Capita

[_Junayetrahman]

Hi all,

I need help on DAX calculating per capita amount for emplyee's.

Based on given example of dataset Per capita amount would be for Employee A is

109003/0.25 = 436,012 (For 2021 year)

For Employee B it would be 

308283.9 / 0.5 = 616,567.8 (For 2022 year)

For Employee C it would be

78531/0.16= 490,818.75 (For 2022 year)

Again Employee C is present in 2023 year

41562.5/0.125= 332,500 for (2023 year)

For 2023 year we have to consider maximum month 8 since we have maximum month available data for 2023 is August.

Total Per Capita Amount is 

537380.4 / 1.035 = 519,208.11

Here is the example dataset:

Payroll INC     Period            YearMonth       Year        Month    Employee name  Employee ID    Type of emp   Amount   Paycode

1/10/2021	2021/10	202210	2021	10	A	25890	Local	29000	Y78IL
1/10/2021	2021/10	202210	2021	10	A	25890	Local	5900	O09PJ
1/10/2021	2021/10	202210	2021	10	A	25890	Local	5689	PO89U
5/11/2021	2021/11	202211	2021	11	A	25890	Local	29000	U8IOP
5/11/2021	2021/11	202211	2021	11	A	25890	Local	2589	P09NB
8/12/2021	2021/12	202212	2021	12	A	25890	Local	29000	TW76A
8/12/2021	2021/12	202212	2021	12	A	25890	Local	7825	EU76B
8/6/2022	2022/06	202206	2022	6	C	78965	Local	39878	JH87GH
8/6/2022	2022/06	202206	2022	6	C	78965	Local	8975	PO134LK
8/7/2022	2022/07	202207	2022	7	C	78965	Local	29678	RT09DP
2/7/2022	2022/07	202207	2022	7	B	57698	Contract	35000	YR09JT
2/7/2022	2022/07	202207	2022	7	B	57698	Contract	5000	YT64YU
1/8/2022	2022/08	202208	2022	8	B	57698	Contract	35000	AS8795
1/8/2022	2022/08	202208	2022	8	B	57698	Contract	6500	JGH670
1/8/2022	2022/08	202208	2022	8	B	57698	Contract	7815	KJ97GH
2/9/2022	2022/09	202209	2022	9	B	57698	Contract	35000	ST53GH
2/9/2022	2022/09	202209	2022	9	B	57698	Contract	983.3	BV87YU
2/10/2022	2022/10	202210	2022	10	B	57698	Contract	35000	NB84KJ
2/10/2022	2022/10	202210	2022	10	B	57698	Contract	35000	AQ23LP
1/11/2022	2022/11	202211	2022	11	B	57698	Contract	35000	ZX32FR
1/11/2022	2022/11	202211	2022	11	B	57698	Contract	7985.6	AS19FG
2/12/2022	2022/12	202212	2022	12	B	57698	Contract	35000	AB46JY
2/12/2022	2022/12	202212	2022	12	B	57698	Contract	35000	WE67WE
2/8/2023	2023/08	202311	2023	8	C	78965	Local	41562.5	AH80YU

Thank you all.

[v-junyant-msft · Accepted solution]

Hi @_Junayetrahman ,

First allow me to correct an error in your calculations:

78531/(2/12)=471186

The Total Per Capita Amount at the end is the same reason why my calculations are off from what you want them to be.
I create a slicer to select 2021, 2022 or 2023.
Please Please refer to the following DAXs:

per capita amount for A = 
VAR Total_amount = 
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        ALL('Table'),
        'Table'[Employee name] = "A" && 'Table'[Year] = SELECTEDVALUE('Table'[Year])
    )
)
VAR Total_month = 
CALCULATE(
    DISTINCTCOUNT('Table'[Month]),
    FILTER(
        ALL('Table'),
        'Table'[Employee name] = "A" && 'Table'[Year] = SELECTEDVALUE('Table'[Year])
    )
)
RETURN
Total_amount / (Total_month / MAX('Table'[Month])

per capita amount for B = 
VAR Total_amount = 
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        ALL('Table'),
        'Table'[Employee name] = "B" && 'Table'[Year] = SELECTEDVALUE('Table'[Year])
    )
)
VAR Total_month = 
CALCULATE(
    DISTINCTCOUNT('Table'[Month]),
    FILTER(
        ALL('Table'),
        'Table'[Employee name] = "B" && 'Table'[Year] = SELECTEDVALUE('Table'[Year])
    )
)
RETURN
Total_amount / (Total_month / MAX('Table'[Month])

per capita amount for C = 
VAR Total_amount = 
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        ALL('Table'),
        'Table'[Employee name] = "C" && 'Table'[Year] = SELECTEDVALUE('Table'[Year])
    )
)
VAR Total_month = 
CALCULATE(
    DISTINCTCOUNT('Table'[Month]),
    FILTER(
        ALL('Table'),
        'Table'[Employee name] = "C" && 'Table'[Year] = SELECTEDVALUE('Table'[Year])
    )
)
RETURN
Total_amount / (Total_month / MAX('Table'[Month]))

Total Per Capita Amount = 
VAR Total_Amount = SUM('Table'[Amount])
VAR Month_A_2021 = CALCULATE(DISTINCTCOUNT('Table'[Month]), FILTER(ALL('Table'), 'Table'[Employee name] = "A" && 'Table'[Year] = 2021))
VAR Month_B_2022 = CALCULATE(DISTINCTCOUNT('Table'[Month]), FILTER(ALL('Table'), 'Table'[Employee name] = "B" && 'Table'[Year] = 2022))
VAR Month_C_2022 = CALCULATE(DISTINCTCOUNT('Table'[Month]), FILTER(ALL('Table'), 'Table'[Employee name] = "C" && 'Table'[Year] = 2022))
VAR Month_C_2023 = CALCULATE(DISTINCTCOUNT('Table'[Month]), FILTER(ALL('Table'), 'Table'[Employee name] = "C" && 'Table'[Year] = 2023))
VAR Month_2021 = CALCULATE(MAX('Table'[Month]), FILTER(ALL('Table'), 'Table'[Year] = 2021))
VAR Month_2022 = CALCULATE(MAX('Table'[Month]), FILTER(ALL('Table'), 'Table'[Year] = 2022))
VAR Month_2023 = CALCULATE(MAX('Table'[Month]), FILTER(ALL('Table'), 'Table'[Year] = 2023))
RETURN
Total_Amount / ((Month_A_2021 / Month_2021) + (Month_B_2022 / Month_2022) + (Month_C_2022 / Month_2022) + (Month_C_2023 / Month_2023))

The results are shown below:

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

[_Junayetrahman]

Thanks for your effort and sorry for my delay in response. What if we have 1000 employe's? It's not possible to calculate each employee per capita. please suggest.