cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dali748 Helper II
Helper II

How to sort row headers in a manual sort in matrix?

category-sort.PNG

 

 

 

 

 

 

 

 

 

 

 

 

Hi, I want to be able to manually sort these Matrix rows in a specific sort that I need it in; for example, I would like "REVENUE" to always be first, so for example:

 

REVENUE

LABOR

TEMP LABOR

SUBCONTRACTORS

ETC....

 

How can I go about getting this data to show in this sort orde

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: How to sort row headers in a manual sort in matrix?

Hi @Dali748 

Sorry, i have not found any simple way of just adding a column.

sorting in matrix may be on ascending or descending, but columns created in edit queries can't change with that selection.

 

I find a more simple way is add two column in edit queries, the step is simple, just use UI,

Capture3.JPGCapture4.JPG

Capture5.JPGCapture6.JPG

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Highlighted
Super User III
Super User III

Re: How to sort row headers in a manual sort in matrix?

Hi @Dali748,

You need to add an auxiliary column with the sort order you need
Revenue - 1
Labor - 2

Then select the column with the descriptions and sort by the column with the number.

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





Super User I
Super User I

Re: How to sort row headers in a manual sort in matrix?

@Dali748 

 

You have to create a number column and key in the sort order they way you want like below.

 

REVENUE 1
LABOR 2
TEMP LABOR 3
SUBCONTRACTORS 4

 

The select the Category column and choose SORT BY and select your sort column.

 

Refer document.

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

 

If it helps, mark it as a solution

Kudos are nice too

Connect on LinkedIn
Dali748 Helper II
Helper II

Re: How to sort row headers in a manual sort in matrix?

Thank you for the replies!

 

@VasTg Where do I create this number column?  Do I have to to it in the data source?

 

For example, I have thousands of rows for "REVENUE", "TEMP LABOR", etc. that roll up to this matrix; do I just have to add a new column in the data source with this number sort break down?  Or can I do this in the "PowerBI>Desktop>Edit Query" screen?

 

Thank you!

Community Support
Community Support

Re: How to sort row headers in a manual sort in matrix?

Hi @Dali748 

Yes, you can add a column in Edit queries.

First sort [Category] column on Ascending, then add an index column from 1,

then add a conditional column(called "ASC") : if [CATEGORY]="REVENUE" then 0 else [Index],

then add another custom column(called "DEC"): if [CATEGORY]="REVENUE" then List.Max(#"Added Conditional Column"[Index])+1 else [Index]

Capture11.JPG

let
    Source = Excel.Workbook(File.Contents("C:24\2.24.xlsx"), null, true),
    Sheet4_Sheet = Source{[Item="Sheet4",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet4_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"CATEGORY", type text}, {"DATE", type date}, {"VALUE", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"CATEGORY", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "ASC", each if [CATEGORY] = "REVENUE" then 0 else [Index]),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "DEC", each if [CATEGORY] = "REVENUE" then List.Max(#"Added Conditional Column"[Index])+1 else [Index]),
    #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"ASC", Order.Ascending}})
in
    #"Sorted Rows1"

 

If you select sort by ascending of [CATEGORY] column, you could select the [CATEGORY] column, then select Sort by Column->select the column [ASC],

Capture9.JPG

If you select sort by descending of [CATEGORY] column, you could select the [CATEGORY] column, then select Sort by Column->select the column [DEC],

Capture10.JPG

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Dali748 Helper II
Helper II

Re: How to sort row headers in a manual sort in matrix?

@v-juanli-msft Thank you for the reply!

 

So I would have to add 3 additional columns (Index, ASC, DES) within Edit queries?

 

Would this be easier if I just added 1 "Category_Sort" column in my raw Excel data using a formula to show the sort order I want the categories in?

 

Thank you!

Community Support
Community Support

Re: How to sort row headers in a manual sort in matrix?

Hi @Dali748 

Sorry, i have not found any simple way of just adding a column.

sorting in matrix may be on ascending or descending, but columns created in edit queries can't change with that selection.

 

I find a more simple way is add two column in edit queries, the step is simple, just use UI,

Capture3.JPGCapture4.JPG

Capture5.JPGCapture6.JPG

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Dali748 Helper II
Helper II

Re: How to sort row headers in a manual sort in matrix?

@v-juanli-msft Ok, thank you! 

 

And this method will also work with multiple rows of the same category; right? For example:

 

USAGE

REVENUE

REVENUE

LABOR

REVENUE

USAGE

USAGE

LABOR

ETC...

Community Support
Community Support

Re: How to sort row headers in a manual sort in matrix?

Hi @Dali748 

As tested, it works in this case.

Matrix would group data based on the "row" field, so we may find the "REVENUE" or "USAGE" only once under "row" list.

 

Best Regards

Maggie

Dali748 Helper II
Helper II

Re: How to sort row headers in a manual sort in matrix?

@v-juanli-msft Ok, yes that would work; as long as it groups it by row in the matrix even if there are multiple rows in the raw data for the same category.  Ty.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors