Forum Discussion

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

Perfomance based on Quartile

I have the attriton Data based on Team Manager and country. I need to find the quartile and based on that we need to see whcih quartile the TM falls under. Fotr this i need a formula that will calcul...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi unnijoy ,

 

 

You will need to create Calculated Columns.

 

Percentile.75 = IF(
Quartiles[Country] = "AUSTRALIA", (CALCULATE(PERCENTILE.EXC(Quartiles[Att%],.75),FILTER(Quartiles, Quartiles[Country] = "AUSTRALIA"))),
(CALCULATE(PERCENTILE.EXC(Quartiles[Att%],.75),FILTER(Quartiles, Quartiles[Country] = "BRAZIL"))))
 
Percentile.5 = IF(
Quartiles[Country] = "AUSTRALIA", (CALCULATE(PERCENTILE.EXC(Quartiles[Att%],.5),FILTER(Quartiles, Quartiles[Country] = "AUSTRALIA"))),
(CALCULATE(PERCENTILE.EXC(Quartiles[Att%],.5),FILTER(Quartiles, Quartiles[Country] = "BRAZIL"))))
 
Percentile.25 = IF(
Quartiles[Country] = "AUSTRALIA", (CALCULATE(PERCENTILE.EXC(Quartiles[Att%],.25),FILTER(Quartiles, Quartiles[Country] = "AUSTRALIA"))),
(CALCULATE(PERCENTILE.EXC(Quartiles[Att%],.25),FILTER(Quartiles, Quartiles[Country] = "BRAZIL"))))
 
 
Category =
SWITCH(
TRUE(),
Quartiles[Att%] < Quartiles[Percentile.25] , "Top Quartile",
(Quartiles[Att%] >= Quartiles[Percentile.25]) && (Quartiles[Att%] < Quartiles[Percentile.5]) , "25th to 50th Quartile",
(Quartiles[Att%] >= Quartiles[Percentile.5]) && (Quartiles[Att%] < Quartiles[Percentile.75]) , "50th to 75th Quartile",
"Bottom Quartile"
)
 
Drag and Drop the Category Column to get the desired results.
 
You can also see difference between Percentile.INC and Percentile.EXC in the below post.
 
 
Regards,
Harsh Nathani
 
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!
 
unnijoy's avatar
unnijoy
Icon for Post Prodigy rankPost Prodigy
6 years ago

hi Anonymous 

 

thanks for the help.

 

can you please help me to modify the formula in such a way that if i have more country. lets say 100 countries. In that situation how to modify this formula. 

I need to find Min and MAX also.

Top Quartile = Attrition % Between MIN and <=25th Quartile

50th Quartile = > 25th Quartile <= 50th Quartile

75th Quartile=>50th Quartile and <=75th Quartile

Bottom = >75th Quartile and <= MAX

 

Please help me to get the formula to match this.

 

Thanks again for your help.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    You also have to state if you want a relative or absolute calculation.

    Best
    D
  • unnijoy's avatar
    unnijoy
    Icon for Post Prodigy rankPost Prodigy
    6 years ago

    Anonymous 

     

    can you please attachthe PBX file also.