Pivot Table Total does not add up

[#1]

I have a if summum funtion

If(Rad_Nat_Code = 'None', Sum(Cost_Per_Unit),

Sum(Cost_Per_National_Unit)

But the total are not adding up at the bottom of the pivot table

can anyone help please

[picturebox]

Hi,
I just had the same problem... and I have the answer ... have a look at my post Problems with Pivot table totals from today ...
you need to use the aggr() function, buit it is all niely explained there.
Petra

[#3]

Thanks but no luck

I have just changed it  to

sum(aggr(if(Rad_Nat_Code = 'None', Sum(Cost_Per_Unit), Sum(Cost_Per_National_Unit)))

but now all my totals have gone !

Thanks for your help .

[swuehl]

You need to add all your table dimensions to the aggr() function dimension list as parameter:

sum(

aggr(

if(Rad_Nat_Code = 'None', Sum(Cost_Per_Unit), Sum(Cost_Per_National_Unit))

, DIMENSION1, DIMENSION2

))

replace the DIMENSION1, DIMENSION2 with your dimensions.

field names are cases sensitive. And check for missing paranthesis.

Hope this helps,

Stefan

[picturebox · Accepted solution]

Hi,
you need to add your dimensions to the function ... like this:

=sum(aggr(if (sum(leh_EU_YTD_CUR)=0, sum(Umsatz2011), sum(leh_EU_YTD_CUR)), Cat2, Cat4, dmListung, IRI_Bezeichnung))

where Cat2, Cat4 etc are the dimensions you want totalled and have set in the pivot. And there is a good section in the help, you need to just locate it... have another look see..

Petra

[hic]

The Aggr-function could work, but it is probably overkill.

Your expression will not work since the condition probably is undefined.

The condition Rad_Nat_Code = 'None' is not defined if there are several values of Rad_Nat_Code. You might want to try

Sum( If(Rad_Nat_Code = 'None', Cost_Per_Unit, Cost_Per_National_Unit))

instead.

Or use dimensions that ensure that Rad_Nat_Code never has more than one value.

[#7]

Thanks adding the demisions worked