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 calculate MIN ,MAX,25,50& 70 Quartile based on country.Fox example We need to calculate the Quartile for Australia(MIN ,MAX,25,50& 70) and based on this we need to know wer the Australian TM falls under. The catogarisation is based on the flllowing.

MIN-25th quartile =Top Quartile, 25th-50th=50th Quartile,50-75=75th Quartile,75-MAX=Bottom Quartile.

Below is the sample data.

Team ManagerCountryAtt%
Rothery, Dylan AUSTRALIA0%
Van Zutphen, Michael AUSTRALIA0%
Coward, Thomas AUSTRALIA0%
Rafter, James AUSTRALIA0%
Wake, Cameron AUSTRALIA0%
Farquhar, Tayler AUSTRALIA0%
Peters, Stacey AUSTRALIA0%
Dodge, Timothy AUSTRALIA0%
Holden-Schulz, Brooke AUSTRALIA0%
Gaerlan, Manuel AUSTRALIA0%
Clifton, Jacqueline AUSTRALIA0%
Moncrieff, Lisa AUSTRALIA0%
Flello, Kelly DeniseAUSTRALIA0%
Kerr, Michael AUSTRALIA0%
Tamaiparea, Kimiora AUSTRALIA0%
Glasse, Shannon AUSTRALIA0%
Leeson, Tricia AUSTRALIA0%
Berry, Susan AUSTRALIA0%
Watkinson, Casey AUSTRALIA0%
Neild, David AUSTRALIA0%
Nyamayaro, Munyaradzi AUSTRALIA0%
Schoeman, Dominique AUSTRALIA0%
Aho, Talitha AUSTRALIA0%
Slattery, Thomas AUSTRALIA5%
Auboire, Ashley AUSTRALIA6%
Baxter, Aurora AUSTRALIA6%
Sgualdino, Adriano AUSTRALIA6%
Dollin, Ellen AUSTRALIA7%
Subbaroo, Punetha AUSTRALIA9%
Harwood, Justin AUSTRALIA9%
Woodfield, Lauren AUSTRALIA12%
Moghaddam, Anthony AUSTRALIA12%
Lyall-Lawrence, CNedra AUSTRALIA15%
Hosking-Thompson, Kelsey AUSTRALIA16%
Najarro, Gabriel AUSTRALIA20%
Skinner, Krista AUSTRALIA24%
Degoumois, Jacqueline AUSTRALIA24%
John Peter, Nirosan Jude AUSTRALIA27%
Munday, Karen AUSTRALIA39%
Duarte, Bruno VieiraBRAZIL23%
Silva, Tamara BRAZIL5%
Silva, Ivan CirinoBRAZIL10%
Ferlini, Thiago Do NascimentoBRAZIL7%
Bonifazio, Anderson BRAZIL34%
Ceccon, Dayane BRAZIL5%
Silva, Haschle NascimentoBRAZIL0%
Santos, Albert Wesley OliveiraBRAZIL67%
Da Silva, Gabriel LimaBRAZIL0%
Pelinson, Leonardo BRAZIL0%
Santana, Evair BRAZIL23%
CAVALCANTE, CLAUDIA DA SILVA BRAZIL7%
Lima, Liliane MariaBRAZIL0%
Pereira, Juliana AlvesBRAZIL1%
Faber, Larissa Vieira Da SilvaBRAZIL7%
Gomes, Leonardo JoseBRAZIL0%
Menezes, Felipe AlexandreBRAZIL5%
De Brito, Thiago Lazaro CorreiaBRAZIL0%
Pena, Bruno BRAZIL0%
Need Help

17 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    It's very easy.

     

    For quartiles, you can either use the DAX functions PERCENTILE.EXC or PERCENTILE.INC or, if you want to get your hands dirty and keep your head busy, you can familiarize yourself with this page:

     

    https://www.daxpatterns.com/statistical-patterns/

     

    This is more than enough information for you to be able to create what you need.

     

    Best

    D

  • 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
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi unnijoy ,

       

      Can you confirm if you are looking at the category values based on the Quaritle25,Quartile50, Quartile75  of that specific country.

       

      Regards,

      Harsh Nathani

       

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

        hi Anonymous 

         

        yes but in that we need to add MIN (attr%)and MAX(Attr%).

         

        So it will be like MIN,Quaritle25,Quartile50, Quartile75,MAX

        Your above formula is correct.

        But that is filtered to only 2 country. Can you modify that in such a way that it can be used on multiple country. We have arround 50 countries.

        And can you please attach the PBX file also. So that it will be helpfull.