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
Greg_Deckler's avatar
Greg_Deckler
Community Champion
10 years ago

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's avatar
ThomasDay
Impactful Individual
10 years ago

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's avatar
    ThomasDay
    Impactful Individual
    10 years ago

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

    • WizardWalksBy's avatar
      WizardWalksBy
      Frequent Visitor
      9 years ago

      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's avatar
        WizardWalksBy
        Frequent Visitor
        9 years ago

        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?