# How to sort pivot table on multiple columns?

• ### Question

• 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

Friday, March 13, 2015 3: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.

More on calculated fields here.

I can provide step by step instructions if needed.

Hope it helps,

Gil

• Edited by Saturday, March 14, 2015 7:34 PM
• Marked as answer by Monday, March 23, 2015 1:31 AM
Saturday, March 14, 2015 7:33 PM

### All replies

• 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.

More on calculated fields here.

I can provide step by step instructions if needed.

Hope it helps,

Gil

• Edited by Saturday, March 14, 2015 7:34 PM
• Marked as answer by Monday, March 23, 2015 1:31 AM
Saturday, March 14, 2015 7:33 PM
• Hi,

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

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"

Thursday, March 19, 2015 1:22 AM
• 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"

Monday, March 23, 2015 1:31 AM