none
How to sort pivot table on multiple columns? RRS feed

  • 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

Answers

  • 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

    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.

    Read more here.

    More on calculated fields here.

    I can provide step by step instructions if needed.

    Hope it helps,

    Gil

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