Sort Row Headers AND Column Headers in Matrix Visualization

[charlesawalsh]

I have a data set grouped into two different sets of ordinal categories that I want to view in a matrix. This is easily achieved in an excel pivot table via manually dragging and dropping the categories into the correct position, but drag and drop is not offered in the PowerBI matrix visual.

I already found and followed one of the several forum posts explaining how to add an extra conditional column with numbers assigned to each categorical value to use for sorting. I added two conditional columns, one for each of the two sets of categories.

However, it appears that numeric sorting solution can be only be applied to either the columns or to the rows, but not to both at the same time.

Did I miss a step? Is there a work around?

Starting from the left the column headers should be ordered: Extra Narrow, Narrow, Mid-Width, Wide, Extra-Wide. 

Starting from the top the row headers should be ordered: Extra-Short, Short, Mid-Length, Long, Extra-Long. 

Its surprising that PowerBI doesn't have feature parity with the Excel pivot table yet. Its almost 2025. 

[rajendraongole1 · Accepted solution]

Hi @charlesawalsh - In Power BI, handling multiple sets of ordinal categories in a matrix visual can indeed feel restrictive compared to Excel's more intuitive drag-and-drop functionality

If sorting issues persist, here are some alternative approaches:Switch to a Table Visual: If the Power BI Matrix Visual is too restrictive, consider using the Table Visual. You can manually create measures or columns that simulate a matrix format.Custom Visuals: Some custom visuals from Power BI’s AppSource (like Advanced Matrix visuals) may offer more flexibility in ordering and sorting categories.Power BI doesn't have Excel's full pivot-table functionality yet, but with the use of sorting columns and proper configuration, you can replicate most of the desired behavior.

Set Sort by Column for Each Category: Power BI allows you to use the "Sort by Column" feature to force the categories into the correct order:For Rows: Select your row category field (e.g., "Length Category") and click on Column Tools → Sort by Column. Select the corresponding conditional column you created for ordering rows (e.g., "Length Sort").For Columns: Similarly, select your column category field (e.g., "Width Category") and sort it by the corresponding sorting column (e.g., "Width Sort").Ensure Matrix Visual Uses These Sort Orders:Add the Length Category to the Rows field in the matrix visual.Add the Width Category to the Columns field in the matrix visual.The sorting should now respect the custom ordering set in your sorting columns.

Hope the above process and approach helps.

[charlesawalsh · Accepted solution]

That worked! You are the man. 

[rajendraongole1 · Accepted solution]

Hi @charlesawalsh - In Power BI, handling multiple sets of ordinal categories in a matrix visual can indeed feel restrictive compared to Excel's more intuitive drag-and-drop functionality

If sorting issues persist, here are some alternative approaches:Switch to a Table Visual: If the Power BI Matrix Visual is too restrictive, consider using the Table Visual. You can manually create measures or columns that simulate a matrix format.Custom Visuals: Some custom visuals from Power BI’s AppSource (like Advanced Matrix visuals) may offer more flexibility in ordering and sorting categories.Power BI doesn't have Excel's full pivot-table functionality yet, but with the use of sorting columns and proper configuration, you can replicate most of the desired behavior.

Set Sort by Column for Each Category: Power BI allows you to use the "Sort by Column" feature to force the categories into the correct order:For Rows: Select your row category field (e.g., "Length Category") and click on Column Tools → Sort by Column. Select the corresponding conditional column you created for ordering rows (e.g., "Length Sort").For Columns: Similarly, select your column category field (e.g., "Width Category") and sort it by the corresponding sorting column (e.g., "Width Sort").Ensure Matrix Visual Uses These Sort Orders:Add the Length Category to the Rows field in the matrix visual.Add the Width Category to the Columns field in the matrix visual.The sorting should now respect the custom ordering set in your sorting columns.

Hope the above process and approach helps.