Impossible Pivot Table ??

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
679
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 ??
:(
 

Some videos you may like

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
Joined
Dec 28, 2004
Messages
58
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,030
Messages
5,569,757
Members
412,289
Latest member
Kingchaos64
Top