Pivot Tables Sorting _ can't sort grand total

[Viktoriya]

Hi All!

I can't sort pivot table. Help me please!

I use the attached file every day. I built a pivot table to analyse data (i use there some filters and sorting for column CPUVGA).
Now I need to sort data by column "Total sum of Stock" but keep data within subtotals (this is grand total for values in column Stock).

So I need: values in columns "Total Sum of Stock", "ACC Sum of Stock" and "Five Sum of Stock" be sorted Z to A within subtotals in column CPUVGA.

Currently when i try to do such sorting i get sorted Z to A only column "ACC Sum of Stock", but i need that sorting would be complited first by grand total ("Total Sum of Stock") and then by partners.

Is it possible?
Maybe i am doing something wrong? Need to clear some filters or something else?

If excel pivot table can't do this, please advise me formulas or other tools to reach the goal.

Thank you in advance!

 

[Vivek D]

See if attached is what you were looking for. I had to move Segment after Part Number to get the sorting right though.

I'd love to see if someone can do it without that as Pivot table sorting is an area I've had a bit of struggle with too.

 

[Viktoriya]

Vivek D

Thank you for your reply.

In fact sorting is working in case PN column contains number values.
How do you think, in case PN column contains text values also, what should i do to sort column "Sum of Stock"?

Please see attached file for more details.

 

[Vivek D]

Click on the filter next to Part Number... Then click on "More Sort Options" and then under descending choose "Sum of Stock".

 

[Viktoriya]

Yes, i did the same and it worked perfect.
But when i started to add more fields to rows it just stopped working...


Are there any restrictions to the number of fields added to rows or columns that enable sorting? or i just have two left hands?

 

[Vivek D]

With Pivot tables, sorting is applied kind of in a hierarchical manner from left to right. At each level of the hierarchy you can specify how the sorting should be done.

So suppose you apply Sum of Stock descending sort to all your columns then it will first take the first item in your hierarchy (Product Group) and sort first by that i.e. Sum of Stock higher to lower, then it will take CPUVGA and do the sorting at that level and so on.

Wish I could explain it better, but if you play around with the sorting for each field and look at the result of that action, you might get an idea.

Start with a fresh pivot table and then apply sort for each column from left to right and you will understand.

If eventually you want the sorting to be applied such that irrespective of other fields you want the part number with the highest sum of stock to be at the top then you should just put part number as your first field (leftmost) and apply the sort.

 

[Viktoriya]

Thank you very much!
Your comment was really usefull!