Sorting a matrix visual based on a measure

[Manish1198]

I have the following setup for a matrix visual as I want to show different measures for different categories. 

All the values are populated using the following measure. Thanks @MFelix  for the help here. Different measures in Matrix visual 

Values = CALCULATE( SWITCH(SELECTEDVALUE('Marix Visualization'[Order]),
1, [SalesForConsumer],
2, SUM(Orders[Sales]),
3, SUM(Orders[Profit]),
4, SUM(Orders[Quantity])), Orders[Category] in VALUES('Marix Visualization'[Category]))

Attaching the pbix file Drive link 
I want to sort the visual based on  Office supplies Quantity descending
Is it possible to do the sorting. 

Thanks

[v-mdharahman]

Hi @Manish1198,

Thanks for reaching out to the Microsoft fabric community forum.

Power BI matrix visuals only allow sorting by the total value of a column, not individual categories inside columns. Since your matrix uses a dynamic measure via SWITCH, it displays different results per category, but Power BI sees all of that as one single measure, so it can’t apply sort logic to just "Office Supplies". 

You can makew the matrix work into sorting based on Office Supplies - Quantity by customizing the total row to return only the Office Supplies value. Try this DAX:
Values Sorted by Office Supplies Quantity =
VAR IsTotalRow = NOT HASONEVALUE(Orders[Category])
VAR OfficeQty =
CALCULATE(
SUM(Orders[Quantity]),
Orders[Category] = "Office Supplies"
)
RETURN
IF(IsTotalRow, OfficeQty, [Values])
Replace [Values] with your existing dynamic measure logic (or wrap the logic into this one). Use this measure instead of the original one in the Values section of your matrix. Then, sort your matrix based on the column total for "Office Supplies". Power BI will use the value returned by the total row (which is now forced to be Office Supplies Quantity), and apply that to sort the entire matrix.

I would also take a moment to thank @danextian and @grazitti_sapna, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[Manish1198]

Hi @v-mdharahman . Thanks for your response. 
As @danextian mentioned, to control the sort order the total displayed is affected. It does not show the total for all categories. The attached images show the expected values vs the values obtained using this measure.
Expected Values:

Replacing Values Sorted by Office Supplies Quantity measure with the existing [ Values]:

Thanks @danextian @grazitti_sapna @v-mdharahman  for your help

[v-mdharahman]

Hi @Manish1198,

As it stands, Power BI’s matrix visual does not allow sorting by a specific inner column value like Office Supplies -> Quantity without manipulating the total, and there's no built-in support for per-column sorting when multiple measures/categories are used dynamically like in your setup.

If maintaining correct totals is a hard requirement, you can consider the following workaround:

Duplicate your matrix visual on the report page. One matrix keeps your current layout and shows the correct totals (with the original [Values] measure). The second matrix is hidden (set to transparent or pushed off-canvas) and used purely to drive the sort order.

In the second (hidden) matrix, use the SortOfficeSuppliesQty helper measure we discussed earlier:
SortOfficeSuppliesQty =
VAR CurrentCity = SELECTEDVALUE(Orders[City])
RETURN
CALCULATE(
SUM(Orders[Quantity]),
Orders[Category] = "Office Supplies",
Orders[City] = CurrentCity
)

Use that matrix to drive the sorting by syncing the row hierarchy (Region > City) and applying descending sort on this helper measure. This way your primary matrix remains visually and numerically accurate, including correct totals. Meanwhile the second (invisible) matrix controls the sort order indirectly.
This trick works well if your data is relatively static (i.e., city list doesn’t change often). If sorting dynamically at runtime is critical and without visuals hacks, the matrix visual unfortunately can’t deliver that fully yet but feel free to vote or track this on the Ideas forum.

Best Regards,

Hammad.

[danextian]

Hi @Manish1198 

A matrix visual with column headers can currently only be sorted based on the column total, not on individual column header categories. To control the sort order, the total must return a custom value, in your case the value of office. Please note that this also affects the total being displayed—it will not show the total for all categories, but only the value for office supplies.

Sample measure:

Officel Supplies Sales  - Total Column =
IF (
    NOT ( HASONEVALUE ( 'dim table'[category column] ) ),
    CALCULATE ( [sales], KEEPFILTERS ( 'dim table'[category column] = "Office Supplies" ) ),
    [sales]
)

[grazitti_sapna]

Hi @Manish1198,

You can create a helper measure to sort the matrix by Office Supplies - Quantity only.

Sort by Office Supplies Quantity =
CALCULATE(
SUM(Orders[Quantity]),
Orders[Category] = "Office Supplies"
)

• Add this measure to your matrix (you can hide the column visually later if needed).

• In the matrix, click on the column header Sort by Office Supplies Quantity.

• Set Descending.

• Then remove the column from the visual if you want, or just hide it.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Manish1198]

Hi @grazitti_sapna 
The helper measure you mentioned cannot be put in columns section. 
When put in Values section, it makes the visual as following. I would need the visual as share in the above request with possiby sorting based on Office supplies quantity.