Quartiles Calculated Columns

[NewbNeedHelp]

Hi! I tried really hard to figure this out myself using the forum, but I just can't. Still relatively new to PowerBI.

Below is an example of what my data looks like, with my desired calculated column. It would calculate quartiles based on the size of Annual Contract value, and within Segments. I also would want a 2nd calculated column that does the same thing but within Segments and Account Group.

Then in my dashboard, I'd be able to filter on those quartiles and do further analysis from there. I do NOT want the quartiles to change though when I, for example, filter on another field like my "Another Attribute" column.

Here is my mocked up PowerBI file, with the full fake data set with 1,000 rows (I can't share real data as it's sensitive). I hope this link works:
 https://www.dropbox.com/scl/fi/5erv1klb6ea5eq97t842y/QuartileHelp.pbix?rlkey=gvkg361ntvljqg8woii7unn... 

I'm hoping this is super easy and I just am too new (dumb?) to figure it out.

Thank you!

Mike

[Daniel29195 · Accepted solution]

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 

best regards. 

[Daniel29195 · Accepted solution]

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 

best regards. 

[NewbNeedHelp]

This works! But I have no idea why!

Thank you!!

[Daniel29195 · Accepted solution]

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

[Daniel29195]

@NewbNeedHelp 

for the second calculated column, 

you just need to add in the ALLEXCEPT function,  THE COLUMN name of  :  account group , 

ALLEXCEPT(Sheet1,Sheet1[Segment],Sheet1[account group])

everything else stay the same.