Forum Discussion

ThomasDay's avatar
ThomasDay
Icon for Impactful Individual rankImpactful 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

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
Icon for Impactful Individual rankImpactful 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"))))