Sort multiple level row header in matrix

Hi FXFelix ,

 

Power BI does not allow you to sort second-level row headers in a matrix alphabetically when the same value appears under multiple first-level groups. To resolve this, you need to create a unique composite key that combines both the first-level and second-level values, and then use a custom sort order.

First, create a DATATABLE that assigns a unique sort order to each combination of CA Type and Catégorie:

FPX_Hierarchie =
DATATABLE (
    "CA Type", STRING,
    "Categorie", STRING,
    "SortKey", INTEGER,
    {
        {"CA Comparable", "Intégrés", 1},
        {"CA Comparable", "Masters Franchisés", 2},
        {"CA Total", "Franchisés", 3},
        {"CA Total", "Intégrés", 4},
        {"CA Total", "Masters Franchisés", 5}
    }
)

Then add a calculated column that concatenates CA Type and Catégorie to make each row unique:

FPX_Hierarchie = 
ADDCOLUMNS (
    FPX_Hierarchie,
    "CompositeCategorie", [CA Type] & " - " & [Categorie]
)

In the table you are visualizing in the matrix, create the same CompositeCategorie column and establish a relationship with the FPX_Hierarchie table based on that field. Then use CompositeCategorie in your matrix row headers and sort it by SortKey. To display only the Catégorie name without the CA Type prefix while preserving the correct sort order, create a new column that retrieves only the Catégorie:

VisibleCategorie = LOOKUPVALUE(
    FPX_Hierarchie[Categorie],
    FPX_Hierarchie[CompositeCategorie],
    'YourTable'[CompositeCategorie]
)

Use VisibleCategorie in your matrix visual and sort it by the SortKey in the background. This approach ensures correct alphabetical sorting within each CA Type group while avoiding duplicate value issues.

 

Best regards,