Power BI Matrix Custom Row Sorting

[Dawg]

I have a matrix where I need to sort the rows in a specific order. How can I do this?

[DataNinja777 · Accepted solution]

Hi @Dawg ,

In Power BI, you can control the row sorting in a matrix by creating a custom column that defines the desired order. If your categories or row labels need to follow a specific sequence, you can add a numeric column that assigns a sort order to each value. For example, you can create a calculated column using DAX:

SortOrder = SWITCH(
    'Table'[Category],
    "Category A", 1,
    "Category B", 2,
    "Category C", 3,
    "Category D", 4,
    99  // Default case
)

Once this column is created, select the original column in the Data View, go to Column Tools, and choose Sort by Column, then select the SortOrder column. This ensures that Power BI follows the defined sequence instead of sorting alphabetically. If your dataset is coming from Power Query, you can achieve the same effect by adding an Index Column that assigns a numeric order to your rows. Then, use the Sort by Column feature to sort the rows in the desired order.

If sorting needs to be dynamic based on a measure, you may encounter limitations in sorting directly within the matrix. In such cases, you can create a ranking measure using RANKX and sort by that measure:

RankMeasure = RANKX(ALL('Table'[Category]), [YourMeasure], , ASC, DENSE)

If Power BI does not allow sorting directly by a measure, you may need to apply additional transformations in Power Query or use calculated columns to enable sorting. Let me know if your sorting scenario is more complex!

Best regards,

[Anonymous · Accepted solution]

HI @Dawg,

Perhaps you can check the following blog about the custom sorting on table/matrix if this help for your scenario:

Custom Sorting in Power BI  

Regards,

Xiaoxin Sheng

[DataNinja777 · Accepted solution]

Hi @Dawg ,

In Power BI, you can control the row sorting in a matrix by creating a custom column that defines the desired order. If your categories or row labels need to follow a specific sequence, you can add a numeric column that assigns a sort order to each value. For example, you can create a calculated column using DAX:

SortOrder = SWITCH(
    'Table'[Category],
    "Category A", 1,
    "Category B", 2,
    "Category C", 3,
    "Category D", 4,
    99  // Default case
)

Once this column is created, select the original column in the Data View, go to Column Tools, and choose Sort by Column, then select the SortOrder column. This ensures that Power BI follows the defined sequence instead of sorting alphabetically. If your dataset is coming from Power Query, you can achieve the same effect by adding an Index Column that assigns a numeric order to your rows. Then, use the Sort by Column feature to sort the rows in the desired order.

If sorting needs to be dynamic based on a measure, you may encounter limitations in sorting directly within the matrix. In such cases, you can create a ranking measure using RANKX and sort by that measure:

RankMeasure = RANKX(ALL('Table'[Category]), [YourMeasure], , ASC, DENSE)

If Power BI does not allow sorting directly by a measure, you may need to apply additional transformations in Power Query or use calculated columns to enable sorting. Let me know if your sorting scenario is more complex!

Best regards,