In many times, I need to sort correctly a chart, a table, etc. For that, I need to have an index column, the best will be to have it from my data source but of course, sometimes it is not the case. In such situation, I will need to create it.

For example, I have this data:

My goal is to create a chart and a table that show the month name sorted correctly. My source is an excel file so I can add easily a month name and index columns but I will show you the hard way. Let’s say that I don’t have access to the source. I will create a “month name” by clicking on “new column”:

Then put this formula:

FORMAT([argument],"MMMM")

NOTE: change “argument” by yours

I will change its name to “month name”:

I will create a chart and a table:

No matter how I will sort, it will not do it correctly so to remediate it, those are the options.

Option 1

For the table, I can add either the “month ID”, the “rank” or the “index” column (see below options) then sort it by clicking on the 3 dots to select those options:

To hide it, first turn off the “text wrap” option for “values” and “column headers” then with the mouse, just reduce the column size until making it disappear:

For the chart, select the “month name” column then click on “column tools -> sort by column” to select “date”:

NOTE: this solution also works for the table, in this case, there is no need to add the “month ID”, the “rank” or the “index” column

Then to sort it correctly, click on the 3 dots to select those options:

The final result:

Option 2

I will create a new column with this formula:

MONTH([argument])

I will name it “month ID” so instead to use the “date” column as an index, I can use this column for the “sort by column”.

Option 3

I will create a new column with this formula:

RANKX(ALL('table'),'table'[argument],,ASC) // to sort descending replace ASC by DESC

NOTE: change “table” and “argument” by yours

I will name it “rank” so I can use this column as an index in the “sort by column”.

Moreover, with the RANKX function, I can create an index for different categories, for instance, I have those 2 additional columns:

I want to have an index for each “priority category”:

RANKX(FILTER('table','table'[argument1]=EARLIER('table'[argument1])),'table'[argument2],,ASC)

And if I want to have it for each “support group” related to its “priority category”:

RANKX(FILTER('table','table'[argument1]=EARLIER('table'[argument1]) && 'table'[argument2]=EARLIER('table'[argument2])),'table'[argument3],,ASC)

NOTE: for additional categories, I just need to add another “&& 'table'[argument3]=EARLIER('table'[argument3])”, etc.

Option 4

For this one, I will have to go to the Power Query Editor by clicking on “transform data -> transform data”:

Here, I will only see the columns from my source and/or columns created in Power Query Editor. To create the “index” column, click on “add column -> index column”:

Click on “home -> close & apply”:

This is the result and as for the other options, I can use this column to sort correctly my data in the “sort by column”: