Sorting a pivot table using "Sort by Expression" isn't working

[mikegrattan]

I have a pivot table with Parent customer as a row dimension and Total 6wkAvg as a Measure. The measure can be changed using a variable,  but I always want the Parent customer to be sorted in descending order by a default measure, such as:

Sum({<ShipDate_DayOfWeek=,$(vSixWk),ShipYear=>}EquivalentUnits)/6

I applied this formula to Expression in the Sort by Expression setting for the Parent dimension, but when I use the variable selector to change the measure to something else, the sorting is not being applied.  

Please advise.

Changed measure, and the sorting is lost (not applied from the expression)

[Bhushan_Mahajan]

@mikegrattan In your expression Sum({<ShipDate_DayOfWeek=,$(vSixWk),ShipYear=>}EquivalentUnits)/6

EquivalentUnits is mentioned so sorting will not work. either you can create variable using pick match and put that variable in expression Sum({<ShipDate_DayOfWeek=,$(vSixWk),ShipYear=>}variable)/6.

So when you pick EquivalentUnits in variable it will automatically take the measure related to it, then if you select EquivalentPrice then it will automatically take the measure related to Price in sort expression measure.

[mikegrattan]

The challenge presented to me is to always sort the table by EquivalentUnits, no matter which measure is selected from the variable selector. Is that possible? I tried with a hidden column but that didn't work either.

[Bhushan_Mahajan]

@mikegrattan Enable only Sort By expression other all sorting based on other parameters disable it and then try, write your sorting expression in sort by expression and disable all other sorts.

[mikegrattan]

That doesn't work either. I think I am going to have to use a third-party Pivot Table from Vizlib; I know that it can handle sorting by a hidden column.