Forum Discussion

_Junayetrahman's avatar
_Junayetrahman
Frequent Visitor
2 years ago
Solved

Per Capita

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/20212021/10202210202110A25890  Local29000Y78IL
1/10/20212021/10202210202110A25890  Local5900O09PJ
1/10/20212021/10202210202110A25890  Local5689PO89U
5/11/20212021/11202211202111A25890  Local29000U8IOP
5/11/20212021/11202211202111A25890  Local2589P09NB
8/12/20212021/12202212202112A25890  Local29000TW76A
8/12/20212021/12202212202112A25890  Local7825EU76B
8/6/20222022/0620220620226C78965  Local39878JH87GH
8/6/20222022/0620220620226C78965  Local8975PO134LK
8/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  Contract983.3BV87YU
2/10/20222022/10202210202210B57698  Contract35000NB84KJ
2/10/20222022/10202210202210B57698  Contract35000AQ23LP
1/11/20222022/11202211202211B57698  Contract35000ZX32FR
1/11/20222022/11202211202211B57698  Contract7985.6AS19FG
2/12/20222022/12202212202212B57698  Contract35000AB46JY
2/12/20222022/12202212202212B57698  Contract35000WE67WE
2/8/20232023/0820231120238C78965  Local41562.5AH80YU

 

Thank you all.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     



2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      _Junayetrahman
      Frequent Visitor
      2 years ago

      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.