Sort X-axis by 2 dimensions

[ReikoK]

Hi!

I have two dimensions ("main brands" and year") in X-axis and I would like to sort car brands by last year investments in descending order leaving the previous years investments under the same car brand. "OTHER" value have to be at the end of the list in the graph.

How it looks at the moment : 

Example, how sorting should look like:

From the forum I found that kind of formula but I am not sure if it could work on my desired outcome: 

NewSort =
VAR thisQty = [Total Qty]
VAR thisouter =
    SELECTEDVALUE ( Stores[City] )
VAR thisinner =
    SELECTEDVALUE ( Products[Subcategory] )
VAR outerrank =
    RANKX (
        ALLSELECTED ( Stores[City] ),
        CALCULATE ( [Total Qty], REMOVEFILTERS ( Products[Subcategory] ) )
    )
VAR innerrank =
    RANKX ( ALLSELECTED ( Stores[City] ), Stores[City], thisinner )
RETURN
    IF ( NOT ( ISBLANK ( thisQty ) ), 100 * outerrank + innerrank )

Sample PBix and data: https://we.tl/t-1Y2odt9GvQ

Thanks in advance!

[ReikoK]

More ideas? 

[v-xuxinyi-msft]

Hi @ReikoK 

Thanks for the reply from @mahenkj2 , please allow me to provide another insight:

I extracted a portion of your data and did the following test. I hope it helps.

I enter a new table called the order table, which serves to give the Main brands columns the order you want them to be in.

Then merge the two tables together

Expand the table to preserve the order column

Because Main brands is a calculated column, and calculated columns cannot be loaded into Power Query, I only gave the order table 12 rows, and the Brand belonging to the OTHERS with an order column of null, replacing the null value with 13.

Select the Main brands column to sort by the order column.

Result:

Best Regards,
Yulia Xu

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

[ReikoK]

Thanks for you help but I need it to be dynamical. That solution outcome is what I need, but once I am going to change years and months, brands are not sorted by last year investments in DESC order

[mahenkj2]

 Hi @ReikoK ,

Use clustred column chart and use year as legend. Experiment as per your need.

Hope it helps.