Custom Sort on Grouped column

[Rahul146]

I have a text column in my query. I grouped it in the data fields and was able to create a matrix. However I need to change the default sort order on this grouped column. Tried reading through the tutorials and searched through the existing posts in the forum, however could not figure out how to achieve it. Appreciate your help.

Sample:

Column A from Query - Seattle, Bellevue, Boston, Quincy, Fremont, Los Angeles, ...

Grouped Column B - WA, MA, CA

Default sort order on Matrix - alphabetical

Desired sort order - MA, CA, WA

[Chris12]

If you want the obvious fix vote for https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/16895230-ability-to-specify-custo...

[Anonymous]

Once you have grouped these items, a new "Field Name (grouped)" field appears in the data table view in PowerBI as a new column in the same table as the original field. Just like any other field, you can set a sort order for this field. 

In your case, like the previous commenter said, you CANNOT use the same sort order field as for the initial field (since values likely aren't distinct). However, that doesn't need to stop you from creating a new sorting field!

For example, create a table that looks like:

State  SortOrder

MA       1

CA        2

WA       3

Then, link this table to your existing table. Then, in the existing table, create a new field called "Sort Order for States" that is just =related(SortOrderTable[SortOrder]). 

Then, that field becomes the one you can use to sort your groups using the "Sort by column" button in the modeling tab. 

[Anonymous]

This worked for me.

Thanks!

[v-sihou-msft · Accepted solution]

@Rahul146

Since your City field is group on State field, which means it has multiple entries for same State in the table, it's not possible to custom a sort order column to have the State column sorted by this numeric column. 

Regards,