Impossible Pivot Table ??

[mrchonginhk]

I have created a Pivot Table.

There are only 2 fields called months and another called Country. There are 2 columns of data called Sales Unit which is the monthly sales volume units and another data column is associated Sales Value.

I created a PT with Country as row field, Sales Unit and Sales Value as Data.

I need to make it such that:-

(1) Bottom line for Sales Unit is AVERAGE of 12 months while
(2) Bottom line for Sales Value is TOTAL of 12 months.

Is this impossible ???

So far I can create one which can show both are AVERAGE or BOTH are SUmmation.

originally I can make the Sales Unit column average.

Hoever, there are many entries in dataset that has field value but zero in data. So when PT takes average, it counts those zero entries in denominator. How to make sure average value is not counted ??

 

[Ceduljko]

Can't understand why is this impossible?

Just right-click on one of the two (B12 or B13), select Field Settings and change Summarize by: to Sum or Avg.

Impossible Pivot.xls
	A	B	C	D	E	F	G
1	Months	Country	SalesUnits	SalesValue
2	01-Oct	NL	1	2
3	01-Nov	NL	2	1
4	01-Dec	NL	1	3
5	01-Oct	UK	2	3
6	01-Nov	UK	3	2
7	01-Dec	UK	2	4
8
9
10			Months
11	Country	Data	01-Oct	01-Nov	01-Dec	GrandTotal
12	NL	SumofSalesUnits	1	2	1	4
13		AverageofSalesValue	2	1	3	2
14	UK	SumofSalesUnits	2	3	2	7
15		AverageofSalesValue	3	2	4	3
16	TotalSumofSalesUnits		3	5	3	11
17	TotalAverageofSalesValue		2.5	1.5	3.5	2.5
18
ImpossiblePivot