Quartiles Calculated Columns

hello NewbNeedHelp 

this is the output of the code : 

this is the output you sent  : 

Column =

var quartile_1 =

CONVERT(

    CALCULATE(

PERCENTILE.INC(Sheet1[Account Contract Value],0.25),

ALLEXCEPT(Sheet1,Sheet1[Segment])

),DOUBLE)

var quartile_2 =

CONVERT(

    CALCULATE(

PERCENTILE.INC(Sheet1[Account Contract Value],0.50),

ALLEXCEPT(Sheet1,Sheet1[Segment])

),DOUBLE)

var quartile_3 =

CONVERT(

    CALCULATE(

PERCENTILE.INC(Sheet1[Account Contract Value],0.75),

ALLEXCEPT(Sheet1,Sheet1[Segment])

),DOUBLE)

return SWITCH(

    TRUE(),

    CONVERT(Sheet1[Account Contract Value],double) <= quartile_1 , 1 ,

    CONVERT(Sheet1[Account Contract Value],double) >= quartile_1 && CONVERT(Sheet1[Account Contract Value],double) <= quartile_2 , 2 ,

    CONVERT(Sheet1[Account Contract Value],double) >= quartile_2 && CONVERT(Sheet1[Account Contract Value],double) <= quartile_3 ,3 ,

    CONVERT(Sheet1[Account Contract Value],double) > quartile_3 , 4

)

 

please try this caluclated column and tell me if it works for you 

NewbNeedHelp 

 im happy it worked out for you  👍

for the logic of how it works ,is as follow : 

so basically we have

q1 which is 25% , 

q2 which is 50%

q3 wwhich is 75%

in dax we dont have quartile, we have percentile, 

so at first, we are getting the value of the 3 quartiles per segement ( t that is why if you noticed im using allexcept(  which remove the row except for segment , since you want it by segment ) 

then , 

in the switch , i compare the value in the current row to the quartiles values that i have calcualted earlier 

hope this make sense, and if it does, please hit that thumbs up button. it would mean alot, 

thanks 

best regards,