Sort two columns on the same one column

[Anonymous]

I would like to create a bar chart with these columns: Category, Subcategory, Quantity

The x axis will be both Category and Subcategory.

The y axis will be Quantity.

I want to sort Category and Subcategory by descending order of Quantity. What this means is Category bars will be from tallest bar to shortest. Then, after drilling down, the Subcategories belonging to each Category will also be sorted from tallest bar to shortest bar.

I've tried:

1. Transform Data > Group by Category with Sum of Quantity as aggregation > Sort > Add Index > Sort Category column with Index 

- this doesn't work because I have other columns in the same table which I'm using as Slicer.

2. The same thing as #1 but on Subcategory 

- doesn't work as well. Same reason as #1

3. Add Category, Subcategory and Quantity in a Table visual > Shift click on Category and Quantity > Change Table visual to Bar chart 

- this doesn't work because although the Subcategories will sort in descending order of Quantity within their respective Category, the Category is not sorted by Quantity but by alphabetical order.

4. The same thing as #3 but shift click on all three columns

- doesn't work because it results in the default sort of the existing data.

I've ran out of ideas. Please help! 😞

Edit: I found the solution on YouTube! Link: https://youtu.be/RDXvpE3V5m0?si=NA5s7BVR5Xu9yy6g

[Anonymous · Accepted solution]

I found the solution on YouTube! Link: https://youtu.be/RDXvpE3V5m0?si=NA5s7BVR5Xu9yy6g

[HarishKM]

@Anonymous Hey,
Kindly use cluster column chart for your requirmeent.

Please refer below example for your reference.

I am following below steps.

select Cluster column chart

drag your cat and sub cat to x axis

drag your QTY measure to value.

select three dot on right side - select sort by QTY then select ASC order to sort as per your requirment.

Thanks

Harish M

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Add snapshop for your reference

[Anonymous]

Hi @HarishKM 

• Unfortunately this will not work for me because if you were to set the subcategories quantities to be different, for example if bb is more than aa but bb2 is less than aa1, the categories would be sorted according to the quantities so the sub category of bb2 will be before aa1, causing the category not sorted by quantity

[barritown]

Hi again,

Here is an advanced version of this approach which should work in more complicated scenarios.

The idea is to create a measure which generates a sorting order value consisting of two parts: the whole part is responsible for sorting categories, the decimal part - for sorting subcategories.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Anonymous]

Hi! Sorry for the incredibly late reply. It doesn't quite work somehow. But good news, i found a video online that solves my problem. Thank you so much for your time !! 

[Anonymous]

Hi @barritown, 

Let me try this and update here again soon. Thank you 

[barritown]

Hi @Anonymous,

I can offer you to create a sorting option like this one:

With your real data it can be more complicated.

Once done, you can add it to Tooltips, then it will be available among the sorting options.

If you drill down, you will get what you want with subcategories:

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Anonymous]

Hi @barritown 

Thanks for this. However, as you've expected, my data is a bit more complicated, hence I'm unable to get the expected result. Thank you very much for your work book as reference too.