Dynamic Sort on multiple Measures do not pose a challenge when it is done on a flat table.
Whereas, while applying sort within section/break, SORT does not happen properly.
As a workaround, we have identified 2 options which are listed below:
Option 1: Using Manage Sort option
Consider a vertical Table with Breaks applied on it.
We need the Table to be arranged based on the first column in it provided the Measure to be sorted based on that column.
For Dynamic Sorting, create a variable with Rank function (with minus operator to make the ascending / descending work correctly for measures) and use that in the table and apply sort on it as below:
Using Alerters, need to display the required measure.
Users can click on the required measure and apply sorting.
We can see that States are sorted in Ascending order based on the Sales Revenue.
Option 2: Using Input Controls
Apply Rank on Section which is driven dynamically & table within each Section for showing KPI Totals
Using Rank function, create 2 variables (Ascending & Descending) for each Measure to be sorted and have Input Controls to drive the Section dynamically.
Sort is enabled at report level.
The most desirable sort can be made as the default one inside the report.