Forum Discussion

ThomasDay's avatar
ThomasDay
Impactful Individual
10 years ago

percentile calculation

  Hello all,   I am trying to create a Percentile Column in a visualization table.  To do this I'm creating a measure that looks at TotalProfitMargin when it's in a table and calculates it's perce...
Need Help
WizardWalksBy's avatar
WizardWalksBy
Frequent Visitor
9 years ago

Did you get this to work, I'm having difficulty with it recognizing the field in the second part  "there's no column named "x"

WizardWalksBy's avatar
WizardWalksBy
Frequent Visitor
9 years ago

here's the code,

 

PercentileRating =
Var UpperMiddle = PERCENTILE.EXC([OT_RATE], 0.75)
VAR TheMedian = PERCENTILE.EXC([OT_RATE], 0.50)
VAR LowerMiddle = PERCENTILE.EXC([OT_RATE], 0.25)
RETURN
SWITCH(
TRUE(),
[OT_RATE] > UpperMiddle, "Q1",
[OT_RATE] > TheMedian, "Q2",
[OT_RATE] > LowerMiddle, "Q3",
"Q4")

 

The Syntax Error is "The value for 'OT_RATE' cannot be determined. Either 'OT_RATE' doesn't exist, or there is no current row for a column named 'OT_RATE'

which is weird because I'm looking right at the column?

  • ThomasDay's avatar
    ThomasDay
    Impactful Individual
    9 years ago

    WizardWalksBy  Here's what I ended up doing.  I created a measure to be used with a selection and display on each row of a matrix is my recollection.

    Tom

     

    PctileCatg =
    //find cutoff points for profit margin quartiles
    VAR LowerMiddle = PERCENTILEX.INC(ALLSELECTED(Facility_Info[Name]), [TotalProfitMargin], 0.25)
    VAR Middle = PERCENTILEX.INC (ALLSELECTED(Facility_Info[Name]), [TotalProfitMargin], 0.50)
    Var UpperMiddle = PERCENTILEX.INC (ALLSELECTED(Facility_Info[Name]), [TotalProfitMargin], 0.75)
    RETURN
    //do nested if to set the category
    IF([TotalProfitMargin] < LowerMiddle , "Q4",
    IF([TotalProfitMargin] < Middle , "Q3",
    IF([TotalProfitMargin] < UpperMiddle , "Q2",
    IF([TotalProfitMargin] >= UpperMiddle, "Q1",
    "NA"))))

    • lannok's avatar
      lannok
      New Member
      8 years ago

      ThomasDay  When attempting to use your logic the only category that returned was "Q1".  In other words, all values in my data set returned with Q1.    Any idea why this would have happened?

       

      QA% = Quota Attainment%:  values range from 0% - 350%

       

      PctileCatg =
      //find cutoff points for QA% quartiles
      VAR LowerMiddle = PERCENTILEX.INC(ALLSELECTED(TBL_8a_Quota_MathWorks_vs_Market[RepName]), [Total_QA_%], 0.25)
      VAR Middle = PERCENTILEX.INC (ALLSELECTED(TBL_8a_Quota_MathWorks_vs_Market[RepName]), [Total_QA_%], 0.50)
      Var UpperMiddle = PERCENTILEX.INC (ALLSELECTED(TBL_8a_Quota_MathWorks_vs_Market[RepName]), [Total_QA_%], 0.75)
      RETURN
      //do nested if to set the category
      IF([Total_QA_%] < LowerMiddle , "Q4",
      IF([Total_QA_%] < Middle , "Q3",
      IF([Total_QA_%] < UpperMiddle , "Q2",
      IF([Total_QA_%] >= UpperMiddle, "Q1",
      "NA"))))

       

      Here's a sample of my data and the returned Q1 value.

       

      Total_QA_%PctileCatg
      0.449709Q1
      0.59135Q1
      0.548306Q1
      0.59508Q1
      0.637852Q1
      0.604238Q1
      0.640328Q1
      0.5061Q1
      0.619239Q1
      0.64759Q1
      0.675069Q1
      0.6615Q1
      0.689282Q1
      0.666738Q1
      1.0149Q1
      0.93Q1
      1.009197Q1
      1.0371Q1
      1.0169Q1
      1.8622Q1
      1.0097Q1
      1.0381Q1
      1.017Q1
      1.038556Q1
      1.01Q1

       

       

      Any help is greatly appreciated.

       

      Thanks,
      Ken