The Sort() function in formulas is not related to the sorting that is used when displaying collections.
It is typically used for when you want to extract the First() or Last(), or do something like Join() as you discovered.
But formulas utilise ‘backend’ functions, and they do not affect the ‘frontend’ display of the result.
Ultimately, the fix will come when it is possible to use the standard relation field component to display a collection of entities which are the result of a formula.
See here.
In that case, users will be able to set frontend filters and sorting on collections which are read-only.
Related: Unable to sort decending