Forum Discussion
Sort X-axis by 2 dimensions
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!
4 Replies
Hi Anonymous ,
Use clustred column chart and use year as legend. Experiment as per your need.
Hope it helps.
Hi Anonymous
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 XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
