Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
simone_g
Contributor II
Contributor II

Pivot table: sorting by expression

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:

Sorting.PNG

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

Thank you in advance!

Labels (1)
  • Other

12 Replies
simone_g
Contributor II
Contributor II
Author

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
Specialist
Specialist

Hi Simone, 

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