Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ThomasDay
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
Greg_Deckler
Super User
Super User

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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

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"

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?

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

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.