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

 

 

Need Help

7 Replies

  • 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!!!