Custom sorting for matrix with stepped column headers

[rajulshah]

Hello everyone,

Here is a sample matrix that I have in my report:

But I want to sort the matrix by the first column, here - Television and then A.
So, here my result should be sorting the Installers as
1. Mobile
2. Thor
3. Sms

Do we have any workaround to sort the matrix by the first column?

Any help would be great.

Thanks in advance.

[Icey]

Hi @rajulshah ,

Please let me know if my latest reply gives you the result you expect.

Best Regards,

Icey

[Icey]

Hi @rajulshah ,

Create a calculated column "Order" in your table and sort the "Installers" column by the "Order" column.

Order =
SWITCH ( 'YourTable'[Installers], "Mobile", 1, "Thor", 2, "Sms", 3 )

Or you can refer to this video to create a custom sort table: Power BI - Custom Sorting in 3 Easy Steps!

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[rajulshah]

Sorry, this is not the solution I want. I don't know who is accepting this as solution.
@v-zhenbw-msft Please investigate.

[Icey]

Hi @rajulshah ,

First, the "accept as answer" operation has been cancelled.

Then, if you do not reply for a long time, we will assume that this reply meets your requirements. If this operation makes you unsatisfied, we apologize to you. 

In addition, could you tell me your specific needs? I'm not very sure if there is something wrong with my reply.

Best regards

Icey

[rajulshah]

Hello @Icey , thank you for the clarification.

My issue is to sort the 'Installer' column here by Category - Television and subcategory A. Please let me know if there is still some confusion.

[Icey · Accepted solution]

Hi @rajulshah ,

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s1PysxJVdJRCknNSS3LLM7MzwNyHIHYXClWB0neJ7GgJL+gGCppiCoZlFqQmFkEZOgCsSWqnGNpSma+grMLqtbg3GJslprgknRC04nNORAZFLeYIUkgOwRhXEhGfhE2l5giy2LYFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Installer = _t, Category = _t, SubCategory = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Installer", type text}, {"Category", type text}, {"SubCategory", type text}, {"Value", Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "CategorySort", each if [Category] = "Television" then 1 else if [Category] = "Laptops" then 2 else if [Category] = "Repair" then 3 else if [Category] = "Audio CDs" then 4 else null),
    JoinTable = Table.SelectRows(Table.Group(#"Added Conditional Column", {"Installer", "Category","SubCategory"}, {{"Sum", each List.Sum([Value]), Int64.Type}}), each ([Category] = "Television" and [SubCategory]="A")),
    #"Merged Queries" = Table.NestedJoin(#"Added Conditional Column",{"Installer"},JoinTable,{"Installer"},"SortTable",JoinKind.LeftOuter),
    #"Expanded SortTable" = Table.ExpandTableColumn(#"Merged Queries", "SortTable", {"Sum"}, {"Sum"})

in
    #"Expanded SortTable"

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.