Average where Grand Total is (calculated field in pivot)

[gnmthead]

Hello,

I am trying to generate a top 10 list of revenue generating customers in a pivot table. The pivot table I have takes into account only the customers with the highest "Grand Total." Vertically listed are customers and horizontally listed are the last 13 months of revenue. I want to consider the average of the last 13 months instead of the sum of the last 13 months so I can capture customers that started 2 months ago (i.e a customer with the highest monthly revenue). I hope this makes sense.

 

[Fazza]

Welcome, gnmthead.

I'm not exactly clear what detail you want in the pivot table and will describe obtaining the top 10 averages.

Suggest after saving the file go via menu data, import external data, new database query (or take the external data option at the first step of the pivot table wizard. Excel file as the source, OK. Browse for your file. If you get a message about no visible cells OK to that and then take 'options' and system tables (worksheets in Excel). OK to that and see the worksheet names listed. Or if you have used a defined name for your data range you will see that and will not need to select from the worksheet name - this is for defined names that are not dynamic. [I have assumed the data has headers in the first row of a worksheet and the data under, that the data values are monthly revenues.] From the worksheet name showing on the LHS select the fields you need and have them appear on the RHS. So, company and revenue. Continue through the wizard until the final step and take the edit in MS Query option. In MS Query select a value in the column of revenues and repeatedly hit the upper case sigma button to scroll through options of Sum then Average. If you go too far, just keep hitting the button and it will come around to average again. (After count, min, max, etc) Now hit the 'Z to A' button to sort the averages in descending order. Now the 'SQL' button and add the " TOP 10 " text into the SQL that appears, near the beginning, just after "SELECT". So it will become something like "SELECT TOP 10 `some reference`.company, etc". OK to this edited SQL and also to a message you will get about not being able to represent it graphically. The data set you see should be the top 10 average monthly revenues. Now the 'open door' button to exit MS Query. Take the pivot table option if you want a pivot table.

HTH. Regards, Fazza

 

[Fazza]

PS

I forgot something important. Likely best to create the pivot table like I described from a new file. Save and close the data file and create the PT in the new file. When you are totally finished, the source file can be opened and the worksheet with the new PT moved into the original source file. This is just to create the PT and avoid memory leak problems. Once created and moved into the original file all should be OK.

F

 

[gnmthead]

Fazza, thanks for taking the time to write this out. I really did learn something new. but what I really need was cell I2 to give me the average (=AVERAGE(B2:H2)) which takes into account blank cells. I hope this makes sense.

A B C D E F G H I
1 CUST 1/08 2/08 3/08 4/08 5/08 6/08 7/08 AVG
2 X 2 3 4 3 2 4 6 3.43
3 Y 12 6 10 4 3 8 7 7.14
4 Z 12 16 17 15.00

 

[Fazza]

Hi, again. I don't really follow. The question was to give a pivot table so there are no formulas.

The formula you posted already gives the average taking into account blank cells. If this is not correct, I don't understand the question.