Pivot Table - match Not Giving Total

[pkelly]

Activity	# of Transactions	Total Transaction Cost (A)	# of Lines	Total Picking Cost (B)	# of Vehicle Drops	Total Delivery Cost (C)	Sales	Gross Profit	Gross Less A, B and C Costs
Back to Back Invoice - Invoice	10	100	30	0	0	0	10,000	2,500	2,400
Collected Order - Invoice	11	110	33	330	0	0	11,000	2,750	2,310
Delivered Order - Invoice	12	120	36	360	1,080	10,800	12,000	3,000	-8,280
Invoice Adjustment - Credit	13	130	39	0	0	0	-13,000	-3,250	-3,380
Invoice Adjustment - Invoice	14	140	42	0	0	0	14,000	3,500	3,360
Manual Credit - Credit	15	150	45	0	0	0	-15,000	-3,750	-3,900
Manual Invoice - Invoice	16	160	48	0	0	0	16,000	4,000	3,840
POS - Refund - Credit	17	170	51	0	0	0	-3,000	-750	-920
POS - Sale - Invoice	18	180	54	540	0	0	18,000	4,500	3,780
Total	126	1,260	378	0	1,080	0	50,000	12,500	11,240

I have the pivot table above...

the issue that I have is that I am not getting totals for the "Total Picking Cost (B)" and "Total Delivery Cost (C)".

"Total Transaction Cost (A)" is giving me a total.

The formula for A is...

=Count(DISTINCT sin_TransactionNumber) * varTransactionCost

Whilst B is...using match as this costs is only applied to specific transaction types

=IF(Match(sin_ActivityAnalysisIoC,'Collected Order - Invoice','Delivered Order - Invoice','POS - Sale - Invoice'), Sum(sin_LineCount) * varPickingLineCost,0)

And C is...using match as this costs is only applied to specific transaction types

=IF(Match(sin_ActivityAnalysisIoC,'Delivered Order - Invoice'), COUNT(DISTINCT if(sin_ActivityAnalysis = 'Delivered Order', %VehicleRunDropKey)) * varDeliveryCost,0)

It is also affecting my main total...

=sum(sin_GrossProfit) - [Total Transaction Cost (A)] - [Total Picking Cost (B)] - [Total Delivery Cost (C)]

How can I get the totals displayed for each colum / affect my total...

Confused

[brenner_martina · Accepted solution]

Hi Paul,

you have to use the function SUM() in every expression at the very beginning, if this does not work, look for function AGGR() in the helpfile.

[pkelly]

Thanks Martina - sometimes the obvious is staring you in the face and you cannot see it........