Legend Sort on Bar Chart

Hi,

 

I have a bar chart where I want the legend to be sorted by the highest total units (from all the y-axis currently shown). Currently, it sorts off of the highest units in one particular client, but I want the legend to show the company with the highest total units on that view at the top. I have circled in red the ideal sort order. Can this be achieved?

Thank you for your time

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Accepted Answer

    your main axis is client, it's confusing what you expect it to look like given that I assume one company could be distributed across multiple clients.

     

    anyway, try sum(sum(amount)) over (partition by company).

     

    you may need to ask your csm to enable the 'add window functions to beast modes' feature switch. 

Answers

  • Thanks, @jaeW_at_Onyx !

     

    What field name should I use for "amount"?

    I tried the below, but I may be making it more complicated than needed:

    SUM(SUM(COUNT(`Item Code`))) OVER (PARTITION BY `Company Name`)

     

    It validated, but when I updated my screen, I got the error: "An issue has occurred during processing. We are unable to complete the request at this time."

    I have requested the feature switch as you mentioned.

     

    Thanks!

    Angela

  • GrantSmith
    GrantSmith Indiana 🔴

    Hi @AJ2020 

     

    Are you including `Company Name` in your graph at all?

  • Hi @GrantSmith ,

     

    Yes, it is the series.

     

    Thanks,

    Angela

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    You should be able to get the sort you want with the built in functionality. 

    In the Chart Properties - General, there are two additional properties that you will want to use in addition to the standard Sorting property:

    Sort on Totals

    Sort Each Category

     

    Play around with these 3 properties (Sorting, Sort on Total, Sort Each Category) and you should be able to get the sort order in the legend you are looking for.

  • Hi @MarkSnodgrass,

    I was hoping the same thing but no luck :(

    This was a drill-down view so maybe that made it more futzy.

    The partition beast mode worked though. 

     

    Thanks for your help ?

  • Hi @jaeW_at_Onyx ,

     

    I adjusted the beast mode to :

    SUM(COUNT(`Item Code`)) OVER (PARTITION BY `Company Name`)

    and it worked! I was making it too complicated ?

    I need to read up on "Partition By" some more.

     

    Thanks so much for your help!

     

     

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    Glad you found something that works.  I have covered window functions so many times on my YouTube channel!

     

    https://www.youtube.com/watch?v=eifSYZIcPzg&t=2247s

    https://www.youtube.com/watch?v=39VHAQEYHAc&t=316s