Dynamic sort is not working

[Milaf]

Hi,

I did a dynamic sort operation with the formula below for Pivot. However, the descending or ascending ones do not work exactly correctly. Where could my error be? Or how can I solve it?

='~A-Z|'
&'sum(Quantity)~ Sort(Descending) ▼ |'
&'sum(Quantity) *-1 ~ Sort(Ascending) ▲'

[Lech_Miszkiewicz]

Hi, 

Your error is that you are not looking at the dimensionality of the pivot table. The sort by expression applies the same order of dimension values accross all levels based on expression used. That being said we cannot tell from your image how many dimensions you have in your pivot table. If you have multiple dimensions then sorting by measure will likely not work the way you imagine it as in Pivot table you are not able to sort dimension values based on measures independently on each nested dimension level. Instead the total sort is being used. 

The only way of actually sorting by measure values is by using "Sort by first measure" option which (yes) - is limited) to only first measure of your pivot table and yes that is the only way of actually sorting by values on all levels

[Milaf · Accepted solution]

If you give 1 to the variable and write the following formula in the sort expression field, it works.

if(vPivotSort=1,
SUM(Quantity) ,
SUM(Quantity*-1))