Total in Pivot table

[upaliwije]

Hi

DESCRIPTION	UNIT_NOS	UNIT_COST	Total Cost
8 Port Switches	65	7,000	455,000
16 Port Switches (Cat6)	29	25,000	725,000
24 Port Switches (Cat6)	16	35,000	560,000
AD Servers	4	170,000	680,000
Total	114	237,000	27,018,000
Correct Total			2,420,000

In my above Pivot table when I apply partial sum, above Total 27,018,000 apper. But the actual Total is 2,420,000/- This may be due to my set expression for Total Cost is Unit_nos* Unit_Cost (114*237,000=27,018,000). Is there any method to show my correct total in the Total Row. If so pls educate me.

[sunny_talwar]

Try this:

Sum(Aggr(UNIT_NOS * UNIT_COST, DESCRIPTION))

[upaliwije]

Then I get following table

DESCRIPTION	UNIT_NOS	UNIT_COST	Total Cost
8 Port Switches	65	7,000	0
16 Port Switches (Cat6)	29	25,000	0
24 Port Switches (Cat6)	16	35,000	35,000
AD Servers	4	170,000	0
Total	114	237,000	35,000

[sunny_talwar]

Well I mean for you to replace UNIT_NOS and UNIT_COST with these actual expressions

Sum(Aggr(UNIT_NOSExpression * UNIT_COSTExpression, DESCRIPTION))

[upaliwije]

Sorry  I dont get what you say

My 3 expression are as follows

SUM(UNIT)

SUM(UNIT_COST)

Sum(Aggr(UNIT * UNIT_COST, DESCRIPTION))  - as suggested by you

[sunny_talwar · Accepted solution]

This is what I meant

Sum(Aggr(SUM(UNIT) * SUM(UNIT_COST), DESCRIPTION))

[hectorvega]

Upali, did you use an if with a Dimensionality()=0 to get the correct sums on the total? 

[upaliwije]

Thanks  a lot