Order by Trellis after limit rows in bar chart


Hey there,


My question is related to this request: https://dojo.domo.com/t5/Card-Building/Limiting-Pivot-Values/td-p/48031 which get a good solution from @jaeW_at_Onyx .


But my further question is that I want to show the data in a bar chart instead of a mega table. I put the suppliers' name on the y-axis ( it is cal 2 shown on screenshot) and put the supplier bucket as a trellis (it is cal 1 shown on screenshot). Then I use the rank function on the blue value in the beast mode to get the rank calculated field and sort it to get the top 20 suppliers. But I could not put all ranked suppliers that belong to the same bucket together. In the below ss, it is only ordered by the blue value instead of by cal 1 and blue value.


Here is an example of what I could get now: 

Domo Question3.PNG

Does anyone have any suggestions on this?


Thank you


  • GrantSmith

    Hi @user034162 


    It sounds like you want the rankings within each col1 subgroup if I'm reading your post correctly. You should be able to get rankings unique to each subgroup instead of the entire dataset by adding col1 to your PARTITION clause

    RANK() OVER (PARITION BY `col1`, `col2` ORDER BY SUM(`amount`))


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


    Thanks for @GrantSmith  answer. I'd like to provide a detailed example to explain my question. Usually, the category2 that belongs to 6month in category1 would get fewer deals than category2 in the other two category1. So I could not rank within category1.


    I give an example in the excel table chart, but I want to create a bar chart in Domo.

    Domo Question4.PNG

    What I want to do is to rank category2 by ordering Deals desc and get the top 5 category2. I could get the highlighted yellow part right now in Domo in the bar chart. But I am thinking if I could change the format and let the bar chart displays like this:

    domo 5.PNG

    They are still the same top 5 category2 but some category2 that belong to the same category1 (like A and C) could be put together by deals desc. I could get this ideal table in Domo megatable by clicking category1 desc, but I could not get this format in a bar chart type in Domo.


    Please give me some advice if anyone knows how to do that.


    Thank you

  • jaeW_at_Onyx

    It's literally covered int the tutorial video ?


    you need to calculate rank in a dataset view (or ETL) then join it on to your activity.

    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"
This discussion has been closed.