Pivot table ranking function always return "1" when using two rows

[VictorBehrens]

Hi There,

I have created a pivot table using data pulled from a data model consisting of two different tables.  It's basically a report that list customer names and their sales for different products.  I have multiple data filter in the table (customer territory, customer type, etc), one item under rows (customer name), and various fields under columns and data.

I have added another column that ranks the sales data used in the pivot table for each row (customer name), and everything works as expected.  However I want to see the customer name and the customer ID number in the report when looking at their sales data and ranking.  As soon as I add another row to the report so that I can see the customer name and number, all the rankings change from their correct values to 1.

I have tried moving the customer number field to various places in the report, but I can't figure this one out?

Please assist.

[#2]

The problem is that ranking works within the combination of ID and name, you can get ranking to work again if you collapse the entire ID field, but that will hide the names. The only way I can think of is to create a combined name and ID string column and use that in the pivot table. You could do that easily in your current query by merging the first two columns.

[#3]

I am on the road from office to home, will download your data and check back and see what is wrong