Forum Discussion

ThomasDay's avatar
ThomasDay
Icon for Impactful Individual rankImpactful 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
Icon for Community Champion rankCommunity 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
    Icon for Impactful Individual rankImpactful 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
      Icon for Impactful Individual rankImpactful 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"