cancel
Showing results for
Did you mean:
Highlighted
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.

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

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

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

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

Proud to be a Super User!

Highlighted
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

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

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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