Sorting pivot table by "show value as"

[xodus52]

For some reason I've been unable to find any resource related to the following problem:
I have a pivot table with two columns of spend amounts ($), but have them displayed as "% of the row total" with the "show values as" option. I want to sort the rows from highest to lowest %, but am only able to sort based on highest to lowest $ amount. If anybody has a solution for this it would be greatly appreciated.

[NBVC]

I seem to be able to sort (in XL2007.. don't have 2010).

Can you post a sample file showing the problem?

[xodus52]

Thanks for the reply; see attached for example pivot.

[NBVC]

I am confused with this sample. What is the issue again?

[xodus52]

I'm trying to sort column B from greatest to least based on percentage value. It will only sort by the sum of spend ($) instead.

[NBVC]

I think I found it...

Try this:

Select the dropdown in A4 (next to row labels)
Select More Sort Options
then click More Options in the dialogue box
Uncheck the box for Sort automatically every time the report is updated
Click Ok
Click Ok

Now you should be able to sort as desired.

[xodus52]

Hmm for a minute there I thought that was solved. That actually works really well, but I have to have the pivot table auto sort each time it is updated by a slicer connection. I tried this scenario and it doesn't look like it will work in that instance. Is there anything else that might work?

[NBVC]

I am not sure then. I don't have 2010 to test the slicer... but I think the only way might be to create a vba event macro maybe that will temporarily engage/disengage the automatic sort feature from the rows area so the refreshing can happen... but I am not the VBA expert to do it, unfortunately....

[xodus52]

Yeah I was hoping to not have to get into VBA. This is part of a dash that will be hosted over sharepoint and rendered "thin", so most extra functionality will be stripped out anyways. If anyone else knows a way that this sort function can be accomplished traditionally, feel free to chime in!

[NBVC]

Here is another possibility.

Add a column (named perhaps Percent) in the source data using formula in D2:

Please Login or Register  to view this content.

copied down.

Then add that column to your pivot table (Sum of Percent)

Format as Percentage and sort that instead.

Does that work?