# Impossible Pivot Table ??

#### mrchonginhk

##### Well-known Member
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 ??

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Ceduljko

##### Board Regular
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
ABCDEFG
1MonthsCountrySalesUnitsSalesValue
201-OctNL12
301-NovNL21
401-DecNL13
501-OctUK23
601-NovUK32
701-DecUK24
8
9
10Months
11CountryData01-Oct01-Nov01-DecGrandTotal
12NLSumofSalesUnits1214
13AverageofSalesValue2132
14UKSumofSalesUnits2327
15AverageofSalesValue3243
16TotalSumofSalesUnits35311
17TotalAverageofSalesValue2.51.53.52.5
18
ImpossiblePivot

Replies
2
Views
209
Replies
1
Views
153
Replies
2
Views
196
Replies
3
Views
637
Replies
0
Views
113