Announcements
cancel
Showing results for
Did you mean:
Not applicable

## Wrong Total Sums in Pivot Table

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.

1 Solution

Accepted Solutions
Not applicable
Author

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))

7 Replies
Not applicable
Author

Hi Moshea444,

I have solved it for you I think.

Can I send you the "new file"?

Regards,

Martijn

Not applicable
Author

you should be able to post it here right?

Not applicable
Author

Not applicable
Author

I will send a screenshot

Not applicable
Author

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

Not applicable
Author

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))

Not applicable
Author

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

Community Browser