Sort measure in pivot table with multiple dimensions

[ruben_moya]

Hi experts,

I haven't been able to find the perfect solution to my problem in the different forums.

Ive got the following table:

Hotels:

Load*Inline [

country, city, chain, name, sales
España, Barcelona, NH, hotel1, 9000
España, Madrid, NH, hotel2, 12000
España, Madrid, Ibis, hotel3, 6500
España, Bilbao, Melia, hotel4, 7500
Italia, Turin, Ibis, hotel5, 20000
Italia, Milan, NH, hotel6, 3500
Italia, Roma, Ibis, hotel7, 19000
];

I need my the measure Sum(sales) to be sorted by different dimensions: 

ie: first by country, then by chain.

I have found the follwoing formulas in forums:

Sort dimension 1

Aggr(sum(sales), [country] )

Sort dimension2

Aggr(sum(sales), [chain] )

The main problem here, as you can see in the picture, is that the the chains of each country are sorted by the total amount of the chain, indpendently from the measure country.

Don't hesitate to ask for questions in case I havent explained myself properly.

Thanks in advanced

[Akshesh_Patel]

hello,

What is the expected result you want to display, I will see if I can produce the desired output from the given data.

Regards,

akki

[ruben_moya]

Hi Akki, thanks a lot for your answer.

That was quite a simple example. I've got 4 dropwdown lists where the user can select different fields.

If the user selects 'country', the values in the field should be sorted by sales and country; 

if the user selects country and city, the values should be sorted first by country, and then within the country, sorted by city;

country,city ,chain ,name , sales
Italia ,Turin ,Ibis ,hotel5, 20000
Italia ,Roma ,Ibis ,hotel7, 19000
Italia ,Milan ,NH ,hotel6, 3500
España ,Madrid ,NH ,hotel2, 12000
España ,Barcelona, NH ,hotel1, 9000
España ,Bilbao ,Melia ,hotel4, 7500
España ,Madrid ,Ibis ,hotel3, 6500

Thanks