Sorting based on a combination of a dimension and a measure based on a rank

Question & Answer
1

Hello,
I have a table where I am displaying two dimensions (Category, Sub Category) and two measures (count and rank) . I need to sort based on Category and the rank so they they line up based on
Order by category ASC, Rank ASC. so that it shows up like below. Currently, seems like I cannot sort it based on Category + rank combination, so the issue is for a given category, the rows with ranking does not show up in sequence ( should be rows 1,2,3 and instead sometimes it shows up as 2,3,1 or other sorting is applied.)

Category Sub Category Count Rank
A xyz 90 1
A ytx 80 2
A xyt 50 3
B frt 200 1
B trf 100 2
B edr 40 3
C drt 80 1
D trf 70 1
D edr 20 2
2

After some trial and error I think I got it to work. :grinning:

Here is my approach - doing it in Quicksight as my numbers are dynamic.
1 Sort the Category in advance how you would like to display and give it a big number for each category say 100000, 90000, 80000… ( My categories are static)
2) add a the category + count
3) if you run into error aggregate, non aggregate mix, use MAX
4) Sort by desc in the chart for category and use the field that you created in step 3 to sort by

3

Glad that worked! Thanks for posting the solution