Every now and then this request comes up, I want to sort by a measure that is not part of the visual. Even though it is not “visualization” best practice sometimes the job requires it anyway 
.
So lets start with the simple one, I want to sort a chart on a measure not part of the visual. Let’s take this visual:
Now instead of sorting by OrderQuantity I want to sort by the ListPrice. The trick here is to make the measure part of the query, and one way you can do that is by adding it to the tooltip. As such:
Now in the sorting option I can also pick ListPrice:
And now the result sorted by ListPrice:
Pretty cool right ! Apparently this trick has been around for some time but I didn’t know until Will and Amanda told me.
Ok next up is sorting in a matrix \ table. This same trick doesn’t work there, although again we need to make it part of the query.
Let’s take the same table of data but now in a matrix, and I also want to sort on ListPrice here.
The trick here is to add listprice to the matrix and sort by it:
And finally hide the column by just dragging the column width to be very small:
Et voila. Pretty neat tricks!
Thank you for sharing these tips. I am attempting to sort a table visual by a column whose data I don’t want to see. Here’s my problem though … I have enabled text wrapping on the column headers. I need it for the other columns, and (so far as I can tell) I can’t differentiate that format by column/field. When I attempt to hide the sort column by narrowing it, the height of the header row increases. Any thoughts on how to address this?
Rename the column to a single character like # and then resize it. Preferably make it the first column in the table.
Great trick!
jajaja Very helpful !!! Very good trick thank you so much Lesia
This is cool feature although I can’t remove sorting. I remember that when you clicked on sorting it was sorting ascending then descending and then sorting was removed. Now it’s not working. When I click first time it’s sorting ascending and when I click again then it’s sorting descending. When I click one more time then it’s sorting ascending again. My version of Power BI is 2.64.5285.741 64-bit (November 2018).
I think you always have had to have sorting, the UI might have changed but you should have always had a sorting option.
Thanks Kasper, you answered a sorting issue that had been bugging me for a couple of days. Really appreciated.
Is there a way to get around the fact that whenever you drill through in the report, the “hideen” column you sorted on gets resized? I went through and turned off all “autosize” options.. I need the column I sorted on to remain hidden 100% of the time.
Unfortunately not 🙁
Hi Kasper
It works with Table but I can’t get it to work with Matrix. I have LocationName in the Rows and there is also a SortOrder field in the same table. I want the rows to be sorted by the SortOrder (it should not be displayed though – only the LocationName should be seen). Any Clues?
Ok this is really the first solution I have found to sort a chart this way quickly, this was so annoying. Many thanks!
I have trickier requirement. I have a matrix with manager names in the rows and months in the columns and utilization in the values. I want to sort this table by the number of people/headcount under each manager (ideally show the Headcount as well) so that the manager with the most headcount shows up first.
Without creating a measure for each month, is there any way to achieve both the sorting as well as the addition of the column so it will be like
Manager Name, Head count, Apr, May, Jun, Jul… , maybe a few other fields. The key is that the months are dynamic.
Thanks for this trick.