Pivot Table - sum AND average in the same field

[velocitygrrl]

Hi there,

I am SURE this must be possible, but I can't for the life of me make it work.

I have a massive pivot table with three different categories of source data listed and subtotaled by a few different fields. Of the three categories of source data, I want two of them to SUM in the subtotals and one of them to AVERAGE.

I know I can change these settings under "Field Settings" but every time I do so, it wants to change all three to SUM, all three to AVERAGE, or all three to SUM and AVERAGE, but i can't seem to sum two and average one, nor can I hide just the subtotals I don't need.

I hope someone can make sense of this simple yet complicated question!!

Many thanks.
v.grrl.

 

[Norie]

Can't you just add the data field again and then set average/sum/whatever?

 

[velocitygrrl]

I don't think I follow.

Of my three source categories, I can properly set each of them to either sum or average, but the subtotals/totals seem to only want to be one or the other..

 

[Norie]

Could you give more information about the data and what you want to do?

What I meant was that if you had a data item that was a sum then you could add that item again but set it to average.

 

[velocitygrrl]

yeah this is a tough one to explain. can i attach a screenshot somehow?

i have three fields across the top, and three types of data
it looks kinda like this:

field 1 field 2 field 3
number1 5
number2 100
% 5%
number1 1
number2 100
% 1%
subtotal field 3 number1 6
subtotal field 3 number2 200
subtotal field 3 % 6%


where you see number1 and number2, i am successfully showing a sum, where you see % i am successfully showing an average

subtotal field 3 is my problem -- i can make all three appear as a sum or an average, but can't show the number1 and number2 as sums and % as an average..

does this make any sense at all??
[/img]

 

[Norie]

You can post an example of your data and required result using Colo's HTML Maker?

 

[velocitygrrl]

ok - it looks like some spaces are causing issues here, but hopefully it's enough to give you the idea. the bit highlighted in yellow is my trouble area..!

2005 Dec 13 - Ad Text Performance.xls
	C	D	E	F
4	KeywordMatching	Keyword	Data	12/1/05
5	Broad	authenticationmethods	Impressions	17
6			Clicks	5
7			CTR	29.41%
8		domainkey	Impressions	3
9			Clicks	0
10			CTR	0.00%
11		senderid	Impressions	16
12			Clicks	1
13			CTR	6.25%
14		senderid	Impressions	6
15			Clicks	0
16			CTR	0.00%
17	BroadSumofImpressions			42
18	BroadSumofClicks			6
19	BroadSumofCTR			35.66%
Sheet1

 

[Norie]

Can you repost after reading the FAQ regarding the HTML maker, the nbsp is a known problem?

Also it might be an idea if you posted an example of the raw data the pivot is based on.

That would give us something to work with.