Legend Sort on Bar Chart

AJ2020
AJ2020 Member
in Magic ETL

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • AJ2020
    AJ2020 Member

    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 Coach

    Hi @AJ2020 

     

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • AJ2020
    AJ2020 Member

    Hi @GrantSmith ,

     

    Yes, it is the series.

     

    Thanks,

    Angela

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • AJ2020
    AJ2020 Member

    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 ?

  • AJ2020
    AJ2020 Member

    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 Coach

    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

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories