Sorting, Ranking doesn't work with multi-row pivot table

[chough10]

I have a pivot table of customers that I use to show revenue totals. In this table, I have columns showing where the customer ranks and the % of total for the year. I also would like to show the country and classification of the country so that it shows up like the following in the pivot table:

Customer Country Class Revenue Rank % of Total

Staples US Retail $100.00 1 3.20%

BoA US Finance $50.00 2 1.60%

The way I have learned to display this is by adding the fields 'country' and 'classification' below the customer in the rows section of the pivot table. Then I switch the report layout to tabular layout and turn off grand totals and subtotals.

The problem is that when I do this it screws up the rankings so that it is ranking them from largest to smallest by customer but it's grouped by class so that since BoA is in a different class, it's ranking also shows 1 and if there was a 2nd retail after BoA it would show 2 despite actually being 3rd. I've checked the base field of the rank column and it says customer still despite grouping by class. Also if I try right clicking the table and resorting it by smallest to largest or anything else it does not sort. If I collapse the fields all the way back to the customer level so that the country and class don't show anything but are still there then the rankings and sorting work fine. But I want the data there.

Is there a way to show the country and class in this format without screwing up all my rankings and still being able to properly sort?

Thanks for any help.

[JeteMc]

I believe that the easiest solution will be to add a rank column to the source data using a function like RANK.EQ
We may be able to provide more specific help if we could see a sample of the data (utilize the instructions in the banner at the top of the page).
Let us know if you have any questions.