Attrition % based on Distinct HC

[unnijoy]

Month	City	Roll Cat	Roll Type	Grade	Ter-type	Ter #	HC#	UID
11-03-2020	Csand	Admin	Op-Admin	G11	Voluntary	2	23	43901CsandAdminOp-AdminG11
11-03-2020	Csand	Admin	Op-Admin	G2	Involuntary	1	22	43901CsandAdminOp-AdminG2
11-03-2020	Csand	Admin	Op-Admin	G11	Involuntary	4	23	43901CsandAdminOp-AdminG11
11-03-2020	Csand	Admin	Op-Admin	G2	Voluntary	7	22	43901CsandAdminOp-AdminG2

Above is my sample DB. I need to calaculate the attrition % from the above table. for this i need to take the HC based on the Distinct UID. So it should worke like Sum(Ter#)/(22+23)

Here we are getting 22 and 23 based on the distinct of UID.

Please help me to get this.

[amitchandak · Accepted solution]

@unnijoy , Create a table like

divide(sum(Table[Ter #]), sumx(Values(Table[UID]), calculate(max(Table[ HC#]))))

[unnijoy]

Hi @amitchandak ,

The formula is workinfg fine.

When i use the same formula for finding the Involuntary or voluntary attrition i find a small mismatch, The issue is that when we use the above formula its is taking the correct Termination Numbers. Bust insted of taking the Over all HC it is taking only the HC based on Voluntarty or Involuntary. As per the above data 

Voluntary % = (2+7)/(22+23).

But cureently its is taking as (2+7)/Hc of Next to Voluntary attrition)

How can i get the Overall HC as denominator.

Voluntary 

[unnijoy]

Hi @amitchandak ,

A help on the above request will be great. Waiting for your reply.