Forum Discussion
Per Capita
- Anonymous2 years ago
Hi _Junayetrahman ,
First allow me to correct an error in your calculations:78531/(2/12)=471186The 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.
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.
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.