Pivot table: sorting by expression

[simone_g]

Hello!

I have a pivot table with two dimensions: Product and Nation. I need to calculate total sales per Product and Nation. For each product, nations should be sorted in decreasing order by expression:

Please see the attachment. How should I change the pivot table to get the desired sorting?

Thank you in advance!

[simone_g]

Thank you.

I tried your expression with Dual but it creates duplicates. For example, I get 2 rows for France for product 2.  Moreover, there is no row for Germany. I am not sure how I shoud sort the calculated dimension in the sorting menu.

[Kushal_Chawda]

@simone_g  Pivot table sorting works on individual dimension, unfortunately there is no option to sort it by measure which is why sorting by expression also doesn't work as expected when you have multiple dimension. Hence, other option is to use calculated dimension as mentioned by @Or  you can try below calculated dimension

=dual(Nation, aggr(sum(aggr(NODISTINCT rank(Product,4,1),Product)*1e5)+sum(Sales), Product,Nation))

then sort your calculated dimension by Numeric descending

[vamsee]

Hi Simone, 

PFA, use the dual while loading data. -Doesn't make sense logically but does the trick.