Forum Discussion

Junayet_Rahaman's avatar
Junayet_Rahaman
Frequent Visitor
2 years ago

Per Capita cal

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 INCPeriodYearMonthYearMonthEmployee nameEmployee IDType of empAmountPaycode
1/4/2022 2022/04    202204     2022 4A25890  Local29000Y78IL
1/4/20222022/04    202204    2022 4A25890  Local5900O09PJ
1/4/20222022/04    202204    2022 4A25890  Local5689PO89U
5/5/20222022/05    202205    2022 5A25890  Local29000U8IOP
5/5/20222022/05    202205    2022 5A25890  Local2589P09NB
8/6/20222022/06    202206    2022 6A25890  Local29000TW76A
8/6/20222022/06    202206    2022 6A25890  Local7825EU76B
2/1/20232023/01    202301    2023 1B57698  Contract35000YR09JT
2/1/20232023/01    202301    2023 1B57698  Contract5000YT64YU
1/2/20232023/02    202302    2023 2B57698  Contract35000AS8795
1/2/20232023/02    202302    2023 2B57698  Contract6500JGH670
1/2/20232023/02    202302    2023 2B57698  Contract7815KJ97GH
2/3/20232023/03    202303    2023 3B57698  Contract35000ST53GH
2/3/20232023/03    202303    2023 3B57698  Contract983.3BV87YU
2/4/20232023/04    202304    2023 4B57698  Contract35000NB84KJ
2/5/20232023/05    202305    2023 5B57698  Contract35000AQ23LP
1/6/20232023/06    202306    2023 6B57698  Contract35000ZX32FR
1/6/20232023/06    202306    2023 6B57698  Contract7985.6AS19FG
2/7/20232023/07    202307    2023 7B57698  Contract35000AB46JY
2/11/20232023/11    202311    2023 11C78965  Local41562.5AH80YU

 

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.