Forum Discussion

unnijoy's avatar
unnijoy
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Attrition % based on Reasons

Hi Guys,

Below is a smaple data base. Based on this i tried to calculate the attriton % (Total Attrits/(sum(Latest month Headcount). The formula is working but the moment when i try to create a graph or table based on Term Reason the value is coimg as Infinity. 

When i check i can see that the termination numbers are comming correct but insted of latest month HC it is coming as 0. How can i fix it.

 

MonthCountryRole CategoryRole TypeGradeReason CategoryTerm ReasonTotal AttritsHeadcountType
October, 2020EYAdminOps AdminG 12VoluntaryPersonal10Inactive
October, 2020EYAdminQualityG 10VoluntaryPersonal10Inactive
October, 2020EYAdminQualityG 10VoluntaryRelocation10Inactive
October, 2020EYAdminQualityG 9AVoluntaryOther Job Opportunity10Inactive
October, 2020EYAgentProductionG 10VoluntaryPersonal10Inactive
October, 2020EYAgentProductionG 11VoluntaryPersonal30Inactive
October, 2020EYAdminOps AdminG 12VoluntaryPersonal0230Active
October, 2020EYAdminQualityG 10VoluntaryPersonal0122Active
October, 2020EYAdminQualityG 10VoluntaryRelocation0122Active
October, 2020EYAdminQualityG 9AVoluntaryOther Job Opportunity0123Active
October, 2020EYAgentProductionG 10VoluntaryPersonal033Active
October, 2020EYAgentProductionG 11VoluntaryPersonal021Active
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi unnijoy 

    Does the Latest Month HeadCount need to be based on Term Reason? If so, measures are:

    HC = calculate(sum('Table'[Headcount]),filter('Table','Table'[Month]>=Max('Table'[Month])))
    Attriton % = SUM('Table'[Total Attrits])/[HC]

     

    If not, try measures:

    Total HC = calculate(sum('Table'[Headcount]),filter(all('Table'),'Table'[Month]>=Max('Table'[Month])))
    Total Attriton % = SUM('Table'[Total Attrits])/[Total HC]

    Is this what you need? Here is the PBIX file.

     

    Best Regards,
    Community Support Team _ Jing Zhang
    If this post helps, please consider Accept it as the solution to help other members find it.

  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    unnijoy  You don't need to change anything but uncheck blank value for Term Reason in visual filters. Just like below.