Pivot Table - Sorting with Totals / Multiple dimensions and Column dimensions

[JPT]

Hello,

We are having issues sorting a Pivot table that contains Totals, multiple Row dimensions and Column dimensions:

Anyone can help us along in sorting every dimension descending? I have attached a sample data qvf.

[brunobertels]

Hi 

Is this order you want :

then if so , 

then in order panel menu 

under continent add 

rank(aggr(Sum( [Amount]),Continent))

under country add 

rank(aggr(Sum( [Amount]),country))

under client add 

rank(aggr(Sum( [Amount]),client))

order will be defined by total column 

hope it helps

[JPT]

Thank you but we are still having issues:

As you can see the place where we have a - / missing value somehow break the sorting within the Pivot table? Any idea? I have attached a new qvf.

[brunobertels]

Hi 

See app upload 

Pivot table is sorting by sum(amoun) ascending on all dimension using calculating dimension and sort by expression 

Dimension : 

continent :

=
aggr(nodistinct
aggr(rank(-sum(Amount)),Continent),Continent)&' '& Continent

Country :

=
aggr(nodistinct
aggr(rank(-sum(Amount)),Continent,Country),Continent,Country)&' '& Country

Client :

=
aggr(rank(-sum(Amount)),Continent,Country,Client) &'-'& Client

then in the sorting panel sort by expression 

Continent =

aggr(
aggr(rank(-sum( {$<Mix=>} Amount)),Continent),Continent)

Country = 

aggr(nodistinct<Mix>
aggr(rank(-sum( {$<Mix=>} Amount)),Continent,Country),Continent,Country)

client 

aggr(
nodistinct
rank(- sum({<Mix=>} Amount),4),Country,Client)

[alanwong1178]

Rank(aggr) works for total column but it does not work with lowest level. In your case , I would like to sort client level as well. 

It is kind of like random sorting in client level even i added rank(aggr(Sum( [Amount]),client)). Any solution for that?

[alanwong1178]

any update?