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
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