Adding index in a calculated table for sorting purposes

[Anonymous]

Hi everyone!

I've used the solution on adding total to a clustered chart from this thread and it worked perfectly. The only thing is that the order of columns is not OK now (see the picture below). Should be 0-2, 3-5, 6-12, etc.

Can anyone advise on how to add an index column to the calculated table 'Table', so I can sort the order of tenure categories on my chart properly?

Thanks.

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

One way is to add the calculated column to tooltips filed of the visual as @tex628 mentioned and click "..." on the visual then sort by calculated column.

Another way is create a custom column in Query Editor and sort the column1 by the custom column (no need to add custom column to tooltips field).

But as you said your table is a calculated table so the second way is not suitable for your current scenario but you might use it someday😁

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Thanks @tex628, @v-jayw-msft!

[v-jayw-msft]

Hi @Anonymous ,

Depending on your data and needs you can use SWITCH() function, IF() function or RANKX() function to create a new column and sort by this column.

switch = SWITCH('Table'[Column1],"0-2",1,"3-5",2,"6-12",3)

if = IF('Table'[Column1]="0-2",1,IF('Table'[Column1]="3-5",2,3))

rank = RANKX('Table','Table'[Column1],,ASC)

 

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Hi @v-jayw-msft,

Thanks a lot for the multiple options! Can you please check my comment on the sorting error above?

[tex628]

Hi, 

Create a calculated column in 'Table' that looks at the different values in [State]. 

Use something like this:

Column = 
SWITCH( [State];
"0-2" ; 0 ;
"3-5" ; 1 ; 
"6-12" ; 2 ; 

etc...

)

Then finally sort your [State] solumn using your new index column. Use the "Sort by column" functionality.

Br,
J

[Anonymous]

Hi @tex628!

I've used the Switch function, but when trying to apply the sorting I got the error below. Can you advise?

Thanks.

[tex628]

My bad, PBI doesn't like it when you try to sort a column with another one that is dependant on the first. 

But the index column that we created isn't completly useless, if you drag it into the tooltip slot in the chart you can then directly sort the chart on that index column. 

If you don't want to have the index column in the tooltip you will need to recreate the table in power query instead, if you do you should not get the issue when you use "sort by column".

If you need any help with setting up the table in power query, just give me the word! 🙂

Br,
J