Forum Discussion

NewbNeedHelp's avatar
NewbNeedHelp
Helper I
2 years ago
Solved

Quartiles Calculated Columns

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=gvkg361ntvljqg8woii7unn15&dl=0 

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

 

Thank you!

Mike

Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    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's avatar
    Daniel29195
    2 years ago

    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,

     

4 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    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's avatar
      Daniel29195
      Community Champion
      2 years ago

      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.

       

       

      • Daniel29195's avatar
        Daniel29195
        Community Champion
        2 years ago

        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,