Forum Discussion
Sorting a matrix visual based on a measure
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.
Hi Anonymous . 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 Anonymous for your help
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.
Hi Manish1198,
As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or did you went ahead and voted on this issue on Ideas forum.
If yes, you are welcome to share your workaround so that other users can benefit as well. And if you're still looking for guidance, feel free to give us an update, we’re here for you.
Best Regards,
Hammad.
Hi Anonymous
Thank you for following up. The issue is still not resolved and I didn't find any workaround that fits my usecase. I couldn't locate the issue on the Ideas forum. If you could share the direct link to the post, I’d be happy to vote on it.
Regards
Manish