percentile calculation

[ThomasDay]

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 percentile.  Not sure if it matters, but TotalProfitMargin is also a computed measure.  Here's the DAX I'm trying to use.   PercentileRating is the measure I'd like to have say what percentile group the row is in.

PercentileRating =
Var UpperMiddle = PERCENTILE.EXC([TotalProfitMargin], 0.75)   `Breakpoints for comparison below
VAR Median = PERCENTILE.EXC([TotalProfitMargin], 0.50)
VAR LowerMiddle = PERCENTILE.EXC([TotalProfitMargin], 0.25)
RETURN

SWITCH (
TRUE(),
[TotalProfitMargin] > UpperMiddle, "Q1",
[TotalProfitMargin] > Median, "Q2",
[TotalProfitMargin] > LowerMiddle, "Q3",
"Q4")

Here is a small table showing TotalProfitMargin. 

I'm getting a syntax error on the VAR part of the code.  I suspect I need to use PercentileX but since it's getting hung up in the VAR statements--I can't go t he next step of debugging the results.

Any help would be appreciated and thanks!

Tom

[Greg_Deckler]

@ThomasDay - If you are trying to put a comment in with the "'Breakpoints for comparison below", the correct syntax is "//". Can you tell us what the syntax error is that you are getting?

[ThomasDay]

I put that comment in for the post only...(but I didn't recall the // syntax for a comment in the code, so thanks!)

The error message reads as below which didn't help me much.  I was surprised it said Median is incorrect--so thought that perhaps the UpperMiddle was fine?  Anyway, here it is...

The syntax for 'Median' is incorrect. (DAX(

Var UpperMiddle = PERCENTILE.EXC([TotalProfitMargin], 0.75)
VAR Median = PERCENTILE.EXC([TotalProfitMargin], 0.50)
VAR LowerMiddle = PERCENTILE.EXC([TotalProfitMargin], 0.25)
RETURN

SWITCH (
TRUE(),
[TotalProfitMargin] > UpperMiddle, "Q1",
[TotalProfitMargin] > Median, "Q2",
[TotalProfitMargin] > LowerMiddle, "Q3",
"Q4")
)).

[ThomasDay]

DOH!!!  Median is an improper variable name as it's a reserved word!  Boy, sorry to bother everyone!!!

[WizardWalksBy]

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]

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]

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

@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.449709	Q1
0.59135	Q1
0.548306	Q1
0.59508	Q1
0.637852	Q1
0.604238	Q1
0.640328	Q1
0.5061	Q1
0.619239	Q1
0.64759	Q1
0.675069	Q1
0.6615	Q1
0.689282	Q1
0.666738	Q1
1.0149	Q1
0.93	Q1
1.009197	Q1
1.0371	Q1
1.0169	Q1
1.8622	Q1
1.0097	Q1
1.0381	Q1
1.017	Q1
1.038556	Q1
1.01	Q1

Any help is greatly appreciated.

Thanks,
Ken