Wrong Total Sums in Pivot Table

[#1]

I have a pivot table with two columns called Sales and Americas Take. If a sale happens outside of the Americas there is still a portion of the sale that is considered part of Americas. This is what the Americas Take number is. Here's a screen shot:

You can see that the totals 215 + 550 + 500 do NOT equal 1350. The graph is showing 1350 because Americas sales is 300 before taking out the Americas Take number (85 in this case)

Here's the Sales column equation:

=if(RegionName='America', sum(SaleAmount)-sum(Total TakeAmount), sum(SaleAmount))

I'm trying to get the sum to show 1265. I have tried the following with no luck:

=if(RegionName='America', sum(aggr(sum(SaleAmount)-sum(Total TakeAmount), RegionName)), sum(SaleAmount))

Any thoughts? Thanks. Attached is the file.

[#2]

Hi Moshea444,

I have solved it for you I think.

Can I send you the "new file"?

Regards,

Martijn

[#3]

you should be able to post it here right?

[#4]

[#5]

I will send a screenshot

[#6]

thanks, but I still want the Sales column to show 215 for america

[#7 · Accepted solution]

Nevermind I got it. You have to do aggr around the whole IF:

=sum(aggr(if(RegionName='America', sum(SaleAmount)-sum(Total TakeAmount), sum(SaleAmount)), LineName,RegionName))

[#8]

are LineName and RegionName some fields? I have kind of the same problem but I'm not able to solve it with the aggr function cause I don't really understand how it works

regards,

MT