Is it possible, without resorting to programming, to sort a pivot table by multiple columns, conceptually similar to a t-sql order by column1, column2?

the following table is a pivot table report which does not sort as desired. I selected a cell in Total to sort ascending, then thought I could select a cell in column 2 to add a secondary sort, but that it is not the case.

Row Labels comes from Row fields, and Column 1, Column 2 comes from Column fields, and Total comes from Values fields.

I thought sorting would be trivial, but alas it is not.

when I sort by only Total, the sort is correct. Sorting by Column 2 then sorts correctly, but it does not preserve the primary sort of the Total column.

Row Labels	Column 1	Column 2	Total
Music	0.071428571	0.071428571	0.142857143
Scent	0.214285714	0.714285714	0.928571429
Layout	0.142857143	0.785714286	0.928571429
Amenities (towels, water, ear buds)	0.142857143	0.785714286	0.928571429
Size		0.857142857	0.857142857
Lighting	0.071428571	0.928571429	1
Flooring	0.071428571	0.928571429	1
Cleanliness		1	1
Total	0.089285714	0.758928571	0.848214286

• Edited by tony bonn 2 Friday, March 13, 2015 4:30 PM

Hi Tony,

There is no trivial way to achieve it. You can create calculated field that will calculate a score that combines the two fields (e.g. =Field1*100 + Field2). Then you can sort by the new calculated field.

Read more here.

More on calculated fields here.

I can provide step by step instructions if needed.

Hope it helps,

Gil

• Edited by Gil RavivMicrosoft employee 11 hours 49 minutes ago

Hi Tony,

There is no trivial way to achieve it. You can create calculated field that will calculate a score that combines the two fields (e.g. =Field1*100 + Field2). Then you can sort by the new calculated field.

Read more here.

More on calculated fields here.

I can provide step by step instructions if needed.

Hope it helps,

Gil

• Edited by Gil RavivMicrosoft employee Saturday, March 14, 2015 7:34 PM
• Marked as answer by George.Zhao CHNMicrosoft contingent staff, Moderator 5 hours 54 minutes ago

Hi,
I'm marking the reply as answer as there has been no update for a couple of days.
If you come back to find it doesn't work for you, please reply to us and unmark the answer.

Thanks

George Zhao
Forum Support
________________________________________
Come back and mark the replies as answers if they help and unmark them if they provide no help.
If you have any feedback on our support, please click "tnfsl@microsoft.com"