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..eg Q1, Q2, Q3, Q4 if quartiles.  

 

TotalProfitMargin is a measure

Hospital_Name is a column in the table

PercentileCategory is the measure I want for the category

 

PercentileCategory =                         

//find cutoff points for profit margin quartiles

VAR LowerMiddle = PERCENTILEX.INC(ALLSELECTED(HOSPITAL10_PROVIDER_ID_INFO[Hospital_Name]), [TotalProfitMargin], 0.25)
VAR Middle = PERCENTILEX.INC (ALLSELECTED(HOSPITAL10_PROVIDER_ID_INFO[Hospital_Name]), [TotalProfitMargin], 0.50)
Var UpperMiddle = (ALLSELECTED(HOSPITAL10_PROVIDER_ID_INFO[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"))))

Here's the error message I get.  Operator or expression '( )' is not supported in this context.

Here's the table and a profit margin ranking as context.

Thanks for your help!

Tom

6 Replies

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