Forum Discussion

Junayet_Rahaman's avatar
Junayet_Rahaman
Frequent Visitor
2 years ago

Per Capita Calculation

Dear All,

I need help with the DAX measure to calculate the Per Capita Amount for employees.
Let's consider Employee A who is present in 2021 for 3 months. In 2021 we have maximum month number 12. So Average people would be 3/12 = 0.25
For Employee B he is present in 2022 for 6 months. So in 2022, we have maximum month in our dataset is 12. So Average people would be 6/12 = 0.5
For Employee C he is present in 2022 for 2 months and maximum month available for 20222 is 12. So Average people would be 2/12 = 0.125
Again Employee C is present in 2023 for 1 month. This time we have maximum month available for 2023 is 8 since we have data till 2023, August. So Average people would be 1/8 = 0.125
Total Per Capita Amount would be sum of amount which is 474,269 and sum of Average people which is 1.035. Then we have to divide 474,269/1.035 = 458,230.91

 

Sample data:

Payroll INC           Period          YearMonth    Year             Month               Emp Name       Emp ID           Emp Type       Amount    Paycode

1/10/20212021/10202110202110A25890  Local29000Y78IL
1/10/20212021/10202110202110A25890  Local5900O09PJ
1/10/20212021/10202110202110A25890  Local5689PO89U
2/11/20212021/11202111202111A25890  Local29000U8IOP
2/11/20212021/11202111202111A25890  Local2589P09NB
3/12/20212021/12202112202112A25890  Local29000TW76A
3/12/20212021/12202112202112A25890  Local7825EU76B
2/6/20222022/0620220620226C78965  Local39878JH87GH
2/6/20222022/0620220620226C78965  Local8975PO134LK
2/7/20222022/0720220720227C78965  Local29678RT09DP
2/7/20222022/0720220720227B57698  Contract35000YR09JT
2/7/20222022/0720220720227B57698  Contract5000YT64YU
1/8/20222022/0820220820228B57698  Contract35000AS8795
1/8/20222022/0820220820228B57698  Contract6500JGH670
1/8/20222022/0820220820228B57698  Contract7815KJ97GH
2/9/20222022/0920220920229B57698  Contract35000ST53GH
2/9/20222022/0920220920229B57698  Contract983BV87YU
2/10/20222022/10202210202210B57698  Contract35000NB84KJ
2/10/20222022/10202210202210B57698  Contract6890AQ23LP
1/11/20222022/11202211202211B57698  Contract35000ZX32FR
1/11/20222022/11202211202211B57698  Contract7985AS19FG
2/12/20222022/12202212202212B57698  Contract35000AB46JY
2/8/20232023/08202308202311C78965  Local41562AH80YU

1 Reply

  • So Average people would be 2/12 = 0.125

    Yeah, no.

     

    sum of Average people which is 1.035

    please explain how you get to that number.

     

     

    Btw there is a typo in your last line of sample data. Month is 8, not 11.

     

    see attached.