How to double sort clustered bar chart visual (2 conditions)?

[alya1]

Hi all,

I have a clustered bar chart showing department # on y axis and total approved budget (light blue) vs total used budget (dark blue) on x axis.

Some departments already spend their whole budget (approved budget = used budget) and I would like those departments to be shown on the bottom of the graph (3, 4, 5, and 6) ideally by the approved budget amount highest to lowest. 
But for departments with leftover budget or used too much, I would like show them on top and ideally sort them by the approved budget amount highest to lowest. 

Is there a way to double sort the axis? 

Thank you!

[Ashish_Mathur]

Hi,

I am not sure how much i can help but i'd like to try.  Please share the download link of the PBI file.

[alya1]

(I can't share the actual data but please see a sample table (sorry not sure how to attach pbix files):

Unit	Approved budget	Used budget
1	62000	3700
2	60000	0
3	45000	45000
4	42000	42000
5	37000	37000
6	35000	35000
7	32000	23000

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[alya1]

thank you so much Ashish! very good idea! 1 more quick question, since I have more units then the sample data, some units where their approved budget = used budget have higher approved budget but larger unit number like below: 

Unit	Approved budget	Used budget
1	62000	3700
2	60000	0
3	45000	45000
4	42000	42000
5	37000	37000
6	35000	35000
7	32000	23000
8	55000	55000
9	60000	60000

In this case when I used your method, the bottom portion where approved budget = used budget is no longer also sorted by the approved budget amount highest to lowest. Any ideas on how to fix this please?

[Ashish_Mathur]

You are welcome.

[alya1]

Ah nevermind! I thought of a small trick!
For the last IF measure,
if yes, instead of listing 0, I listed [App]
if no, I listed [App] + random huge number like 1000000 😄 

[Shosher]

So im not 100% sure but i think this might work. 

Click on your visual and turn it into a table. 

Hold the shift button and sort it by as many columns as you want by clicking on the headers. 

Turn it back into a visual. If all went well this would've done it!

[alya1]

Sadly it didn't but thank you for the idea!