Problem with Pivot sorting

[kerekesm]

Hello there!

I created a Pivot with values, columns and more data, but I have problems with the rows, so I will just focus to the rows through this basic example.
I would like to use this Pivot under different circumstances. Sometimes I would like to sort by the 'COMPONENT_PART' another time by the 'COMP_SUPPLY_CAT'.
Unfortunately I'm only able to sort by the first column. Which means I can sort by 'COMPONENT_PART' but after that I can not sort by 'COMP_SUPPLY_CAT', I clik on sort and it does nothing. Of course I can move 'COMP_SUPPLY_CAT' to the beginning, but the situation remains the same, now I cannot sort by 'COMPONEN_PART'.
One interesting addition that under the 'More sorting option' panel I can only see the actual first column.

Could anyone help me with this issue? Or maybe it is not possible to sort by multiple rows?

Thanks in advance,
Mark

 

[Alex Blakenburg]

Pivot tables are designed to work with grouping. What this means for sorting is that it sorts within groups from left to right.
So column 2 will sort but within each group of column 1, and column 3 will sort within the column 2 grouping (which in turn is sorted within column 1 grouping)
In your picture the your Columns A,B,C combinations are unique so a subsort will not seem to do anything.

Have you considered using Power Query to provide a summary similar to what table would give you but in a table format so you can still sort it any way you want to ?
It will reset to the original Query sort when you refresh the Query but in the meantime you can sort the way you want.

 

[kerekesm]

Hello Alex,

thanks for the answer, I'm not familiar with the Power Query, but I will give it a try.

 

[Alex Blakenburg]

Come back to us if you need help with Power Query.