topic Re: Sorting a pivot table using "Sort by Expression" isn't working in App Development

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

mikegrattan — Wed, 29 Jan 2025 21:14:02 GMT

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)

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

Bhushan_Mahajan — Thu, 30 Jan 2025 05:11:05 GMT

@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.

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

mikegrattan — Thu, 30 Jan 2025 15:07:50 GMT

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.

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

Bhushan_Mahajan — Fri, 31 Jan 2025 03:54:56 GMT

@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.

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

mikegrattan — Fri, 31 Jan 2025 15:53:58 GMT

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.