Forum Discussion
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/2021 | 2021/10 | 202110 | 2021 | 10 | A | 25890 | Local | 29000 | Y78IL |
| 1/10/2021 | 2021/10 | 202110 | 2021 | 10 | A | 25890 | Local | 5900 | O09PJ |
| 1/10/2021 | 2021/10 | 202110 | 2021 | 10 | A | 25890 | Local | 5689 | PO89U |
| 2/11/2021 | 2021/11 | 202111 | 2021 | 11 | A | 25890 | Local | 29000 | U8IOP |
| 2/11/2021 | 2021/11 | 202111 | 2021 | 11 | A | 25890 | Local | 2589 | P09NB |
| 3/12/2021 | 2021/12 | 202112 | 2021 | 12 | A | 25890 | Local | 29000 | TW76A |
| 3/12/2021 | 2021/12 | 202112 | 2021 | 12 | A | 25890 | Local | 7825 | EU76B |
| 2/6/2022 | 2022/06 | 202206 | 2022 | 6 | C | 78965 | Local | 39878 | JH87GH |
| 2/6/2022 | 2022/06 | 202206 | 2022 | 6 | C | 78965 | Local | 8975 | PO134LK |
| 2/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 | 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 | 6890 | 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 | AS19FG |
| 2/12/2022 | 2022/12 | 202212 | 2022 | 12 | B | 57698 | Contract | 35000 | AB46JY |
| 2/8/2023 | 2023/08 | 202308 | 2023 | 11 | C | 78965 | Local | 41562 | AH80YU |
1 Reply
- lbendlinSuper User
So Average people would be 2/12 = 0.125Yeah, no.
sum of Average people which is 1.035please 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.