cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Impactful Individual
Impactful Individual

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

RankMeasure.PNG

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

 

 

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: percentile calculation

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Impactful Individual
Impactful Individual

Re: percentile calculation

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")
)).

Highlighted
Impactful Individual
Impactful Individual

Re: percentile calculation

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

Highlighted
Frequent Visitor

Re: percentile calculation

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"

Highlighted
Frequent Visitor

Re: percentile calculation

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?

Highlighted
Impactful Individual
Impactful Individual

Re: percentile calculation

@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"))))

Highlighted
New Member

Re: percentile calculation

@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

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors