Forum Discussion

ThomasDay's avatar
ThomasDay
Impactful Individual
10 years ago

Percentile category

Hello again,   I'm looking to compute the percentile breaks in a visualization (filtered) table dynamically--change the slicer, things change...and then classify each row into a percentile category...
eljustino's avatar
eljustino
New Member
10 years ago

Huh... other than a typo in the VAR UpperMiddle line (missing the PERCENTILEX.INC function), it worked for me with my mini sample version:

 

 

Here's the exact version:

PercentileCategory = 
//find cutoff points for profit margin quartiles
VAR LowerMiddle = PERCENTILEX.INC(ALLSELECTED(HOSPITALS[Hospital_Name]), [TotalProfitMargin], 0.25)
VAR Middle = PERCENTILEX.INC (ALLSELECTED(HOSPITALS[Hospital_Name]), [TotalProfitMargin], 0.50)
Var UpperMiddle = PERCENTILEX.INC (ALLSELECTED(HOSPITALS[Hospital_Name]), [TotalProfitMargin], 0.75)
RETURN
//do nested if to set the category
IF([TotalProfitMargin] < LowerMiddle , "Q1",
IF([TotalProfitMargin] < Middle , "Q2",
IF([TotalProfitMargin] < UpperMiddle , "Q3",
IF([TotalProfitMargin] >= UpperMiddle, "Q4",
"NA"))))
  • Sean's avatar
    Sean
    Community Champion
    10 years ago

    eljustino

    How do you get overall TotalProfitMargin in the bottom row of 81.09%???

     

    In the sample I created I get 40.98% 

    • eljustino's avatar
      eljustino
      New Member
      10 years ago

      Sean Ah, yeah... that's actually the MAX (used MAX for my simple measure). Probably should've just removed that, but I wasn't paying attention.

      • Sean's avatar
        Sean
        Community Champion
        10 years ago

        Okay I didn't think about this - that makes sense now!