column chart custom sort x-axis

[bobbob123]

Hi,

I have a column chart that has the following x-axis.

I want to arrange the x-axis of my column chart to go as follows.

1,2,...10,11a,11ai,11aii...11av,11b,11bi,11bii....11bv,.......11g,11gi,11gii ... 11gv, 12a ........ 20.

Each question after 11 has part a to g and subparts up to v. 

[PaulDBrown · Accepted solution]

For the sorting to be dynamic you would need to create a measure using RANKX, and that needs an expression to rank by. 
Another option is to create the order in Excel, which is much more flexible for these types of tasks. You can then either use that as your table or merge with the table already loaded.

[PaulDBrown]

You need a column in the axis table which establishes the order you need.  If the values are in the right order by default, you can add an index column in Power Query. If not you will need to create the order with a conditional column in Power Query or using SWITCH in the table.

[bobbob123]

Hi Paul, thanks for your feedback. I am aware of that approach i.e create a new column and establish the order however I am interested in a dynamic order.

My questions range from 1 to 20, where 1-10 are numerical, 11-20 has parts a-g and each parts have i-v. 

Using your approach, I would have to list out all the questions i.e 1,2.....10,11,11a,1ai,....11av,11b,.....11g,.. all the way up to 20gv which is long and teidous.