Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I need help in calculating overall YTD attrition %. Below is the data
Site | Month | Total Attrition | HC | YTD Attrition % |
Site 1 | 1-Sep-19 | 13 | 612 | 25% |
Site 2 | 1-Sep-19 | 7 | 412 | 20% |
Site 1 | 1-Oct-19 | 15 | 716 | 25% |
Site 2 | 1-Oct-19 | 11 | 504 | 24% |
Site 1 | 1-Nov-19 | 19 | 709 | 28% |
Site 2 | 1-Nov-19 | 14 | 508 | 27% |
Site 1 | 1-Dec-19 | 18 | 684 | 29% |
Site 2 | 1-Dec-19 | 9 | 518 | 25% |
Site 1 | 1-Jan-20 | 15 | 609 | 29% |
Site 2 | 1-Jan-20 | 12 | 486.5 | 26% |
Site 1 | 1-Feb-20 | 23 | 519 | 32% |
Site 2 | 1-Feb-20 | 8 | 437 | 26% |
Site 1 | 1-Mar-20 | 9 | 452 | 31% |
Site 2 | 1-Mar-20 | 6 | 436 | 24% |
Site 1 | 1-Apr-20 | 3 | 434 | 29% |
Site 2 | 1-Apr-20 | 1 | 464.5 | 22% |
Site 1 | 1-May-20 | 2 | 436 | 27% |
Site 2 | 1-May-20 | 2 | 492 | 20% |
Site 1 | 1-Jun-20 | 5 | 411 | 26% |
Site 2 | 1-Jun-20 | 3 | 496 | 18% |
Site 1 | 1-Jul-20 | 6 | 388 | 26% |
Site 2 | 1-Jul-20 | 2 | 484 | 17% |
Site 1 | 1-Aug-20 | 4 | 388 | 25% |
Site 2 | 1-Aug-20 | 2 | 483 | 15% |
Site 1 | 1-Sep-20 | 3 | 355 | 10% |
Site 2 | 1-Sep-20 | 1 | 347.5 | 3% |
Site 1 | 1-Oct-20 | 4 | 355 | 12% |
Site 2 | 1-Oct-20 | 3 | 345 | 7% |
The calculation on excel goes as below (for FY21, i.e. from Sep'20):
YTD % = (Cumulative Sum (Total) / Cumulative Average (HC)) * (12/2) ----- divided by 2 because FY21 starts from Sep'20
Cumulative sum and average would start from Sep'20 as well. So in short if i have to calculate Overall YTD attrition % for both sites the formula would be,
YTD % = (3+1+4+3) / Average (355+355, 347.5 + 345) * (12/2) = 9 %
Also it is done cumulatively so that when we map the monthly attrition it should give us the YTD attrition for that month.
Let me know if i am unclear and need to explain it in a better way.
Looking for help in this.
@Anonymous
Just not clear, regarding to the solution, do you want to create a measure of an overall YTD attrition or just FY 21 Ytd? Can you just show the expected output in a table and the logic?
I don't understand the excel calculation logic, why do you divide by 2 if for FY21 that starts on Sep 20, what about FY20, and FY19? Also, do you calculation site 1 and site 2 together?
Regards
Paul
Yes, i want to create a measure for overall YTD attrition %. That way if i map the attrition % for the last 12 months then till Aug'20 it should be calculated basis FY20 and from Sep'20 the calculation should be basis FY21.
For FY21 i have divided by 2 since i have data until Oct'20 which is the 2nd month in FY21. Similarly when we move on to Nov we will divide it by 3 and so on. I need the calculation for both sites, i.e. if both sites are selected it should calculate overall by adding respective numbers and if a single site is selected then calculation should be shown for that site.
I hope i am clear. Let me know if you have any further questions
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
101 | |
78 | |
69 | |
63 |
User | Count |
---|---|
141 | |
106 | |
101 | |
85 | |
72 |