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

[Dali748]

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

[VasTg]

@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

[Dali748]

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!

[v-juanli-msft]

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]

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

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

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]

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

[v-juanli-msft · Accepted solution]

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,

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.

[deneme]

I prefer to do this in powerbi with measure or calculated column method as powerquery causes performance slowdown. Also, column headings cannot be sorted with what is explained here!

Example column names:
"A B C D"
Names of desired columns:
"D B A C"

I think you didn't understand the main point.

[Dali748]

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

[v-juanli-msft]

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]

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

[MFelix]

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.