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) ...
  • 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.