Excel Pivot Table Help

[Zakalwe]

I have a simple PivotTable - Customers across the top, Salespeople down the side, each sale is the Sum of that Salespersons business with that Customer. I’m trying to do an external calculation that gives each Salesperson points based on their sales as a percentage of the max sales to that customer so:

If Salesperson A has $1,000 in sales to Customer 1 and the highest sales to that customer is $2,000, then Salesperson A gets .5 pts. The salesperson with $2,000 gets 1 pt and so forth.

Now, I’ve figured out how to make the points chart flexible in that it looks for the specific salesperson name and the specific customer name like this:

=GETPIVOTDATA(“Total”,‘RevenueReport’!$A$1,“Salespeople”,$A3,“Customer”,D$2)

where $A3 is the Salesperson’s Name on the Points sheet and D$2 is the Customer’s Name. This is so that if a refresh of the table moves people/customers around, the Point chart doesn’t break.

The problem I have is that I can’t seem to get the MAX calculation to be self-adjusting the same way. Ideally, it would look something like:

=GETPIVOTDATA(“Total”,‘RevenueReport’!$A$1,“Salespeople”,MAX(…),“Customer”,D$2)

So that it would “lookup” that customer and then return the MAX from the Total value for that customer.

So, is there anyway to do what I want?

[Zakalwe]

One time bump.

I’m curious, is that I didn’t give enough information or does no one have an answer? I’m hoping it’s the former, but afraid it’s the latter…

[CookingWithGas]

I am looking over your question and I think you have done a better job than most at describing what you’re doing. But I do have a couple of questions.

What does your source data look like? Based on your description I would guess that you have a row with each sale, and columns for salesperson, customer, and amount. How close am I? I suspect that you can use your source data to do what you need without the pivot table. Do you need the pivot table for other purposes that you didn’t describe here?

Meantime I am prototyping your description to see what I can figure out.

[Gangster_Octopus]

I don’t think there is any easy way to do what you ask, GETPIVOTDATA gets a data point, not a range. However, if I may ask, shouldn’t the max be independent of reordering?

[TroutMan]

Not sure if this will help you, but if your pivot table doesn’t show totals by customer, you can use a simple MAX function in your Points worksheet to get to get the maximum for each customer:

=MAX(‘RevenueReport’!B:B)

where column B has the data for Customer 1. It isn’t necessarily dynamic as the pivot table changes, but if your Points worksheet also uses a reference for the customer name (i.e. point to cell B2 or whatever on the RevenueReport worksheet), it will at least change along with the pivot table.

If you do need totals in your pivot table, you could create a second one that is identical except that it doesn’t show totals for columns.

I made some assumptions about how your worksheets are set up, so apologies if this isn’t applicable at all.

[J-P_L]

For assistance with Excel, you may want to look at this site

Mr Excel

I saw him on TV doing a presentation specifically about Pivot Tables, I must admit that it went over my head.

[Zakalwe]

Okay! I’m going to try to answer all of the questions in one post. I’m just ambitious that way…

CookingWithGas, the table has Salesmen listed in Column A, Row 2 (starting in Col B) is the Customers. Each cell in the range is a salesman’s total with the given customer. I have totals at the end of each Row and Column.

Gangster Octopus, yeah the Max is independent of order, but only within a given row or column. One of the issues is that I have the basic PivotTable on one sheet. I then have a second where all of the calculations occur. Since not all of our customers order every month, the customers move around as the PivotTable updates (and some don’t even show for a month or two). This causes problems in the secondary sheet. Since I’m always finding the Max for Column B (for example), yet the values are for the customer that used to be in column B, not the current one.

TroutMan, that’s actually how I’m doing it now. The problem is it’s not dynamic and it’s a bit of a pain in the ass to change it every month.

J-P L, thanks for the link! On first blush, it doesn’t appear to have a solution to my problem, but it has some other neat stuff and I’ll keep looking.

[TroutMan]

You could do it with a macro that dynamically creates the formulas on the Points sheet. I think the other posters are right that it isn’t possible using only formulas.

[CookingWithGas]

@Zakalwe

CookingWithGas, the table has Salesmen listed in Column A, Row 2 (starting in Col B) is the Customers. Each cell in the range is a salesman’s total with the given customer. I have totals at the end of each Row and Column.

I’m not quite getting it. What you describe here sounds like what your pivot table looks like. But I want to know what the raw data looks like–the data that your pivot table is built from. Normally a pivot table is built from a list of unnormalized data.

[Gangster_Octopus]

There is a way in a pivot table to return the max of a colum or row. When creating the pivot table usually it creates a row or column titled “Grand Total” at the bottom or end. Right click on it and clik on “Field Settings” and there you can choose MAX.

Does that help?

[TroutMan]

@Gangster_Octopus

There is a way in a pivot table to return the max of a colum or row. When creating the pivot table usually it creates a row or column titled “Grand Total” at the bottom or end. Right click on it and clik on “Field Settings” and there you can choose MAX.

Does that help?

I should just wait for Zakalwe to clarify, but I can’t resist speculating. I assume the source data is a table of salesperson, customer, and sales amount (among other columns like date). One salesperson can have multiple sales to the same customer, which is why you need a pivot table to sum the sales per customer and salesperson. So simply getting the MAX doesn’t help - that would just give the top single sales event for a customer, not the total of sales for the customer/salesperson.

I’m going to stick with needing a macro to make this dynamic.

[Gangster_Octopus]

@TroutMan

I should just wait for Zakalwe to clarify, but I can’t resist speculating. I assume the source data is a table of salesperson, customer, and sales amount (among other columns like date). One salesperson can have multiple sales to the same customer, which is why you need a pivot table to sum the sales per customer and salesperson. So simply getting the MAX doesn’t help - that would just give the top single sales event for a customer, not the total of sales for the customer/salesperson.

I’m going to stick with needing a macro to make this dynamic.

OK,yeah you’re right. For some reason I thought it just changed the bottom row metric.

[Zakalwe]

@TroutMan

I should just wait for Zakalwe to clarify, but I can’t resist speculating. I assume the source data is a table of salesperson, customer, and sales amount (among other columns like date). One salesperson can have multiple sales to the same customer, which is why you need a pivot table to sum the sales per customer and salesperson. So simply getting the MAX doesn’t help - that would just give the top single sales event for a customer, not the total of sales for the customer/salesperson.

I’m going to stick with needing a macro to make this dynamic.

You are correct about the structure of the source data and the layout of the Pivot.

I do think I’ve figured how to do it (I think - I didn’t have time to try my solution today). I’m going to use a series of column references, VLOOKUPs, and INDIRECTS.

[Zakalwe]

So, final post just to thank everyone for the suggestions and help.

I’ve solved the problem via a total cheat. I forced the base query into a combination outer join so that it returns values for all salesperson/company combinations. Since the width and depth of the list is now permanently fixed, finding the various MAXes is easy.

Again, thanks for the assistance.