cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rahul146
New Member

Custom Sort on Grouped column

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

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: Custom Sort on Grouped column

@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,

View solution in original post

4 REPLIES 4
Moderator v-sihou-msft
Moderator

Re: Custom Sort on Grouped column

@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,

View solution in original post

zacksg1 Advocate I
Advocate I

Re: Custom Sort on Grouped column

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. 

vivianloi
Frequent Visitor

Re: Custom Sort on Grouped column

This worked for me.

 

Thanks!

 

Highlighted
Chris12
Frequent Visitor

Re: Custom Sort on Grouped column

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors