Special Case For One Row In Pivot

[#1]

I have a pivot table shown below. It breaks sales down by regions and sales lines. It shows the sum of the sales for different region-line combinations. This works ok but I have another requirement that i'm not sure how to meet. The last column is called America's Take. Even if a sale is done in another country there is some amount that is still considered part of Americas. The requirement is that the Americas sales subtract the Americas Take values from it's total. So the first row that says "America | Sales1 | 300 | 0" should be "America | Sales1 | 215 | 0"

I've tried doing this for the Sales expression:

=
SUM(SaleAmount)
-
if(REGION_ID = 1, SUM({$<REGION_ID=>}TakeAmount), 0)

with no luck.

Attached is a example QVW. Thanks!

[matt_crowther · Accepted solution]

Soloution attached - hopefully!

Good old 'Total' to the rescue.

Hope that helps,

Matt - Visual Analytics Ltd

[#3]

Thanks so much. Whenever I ask a question the solution seems so simple but I never have any clue. Thank god for this forum.

[#4]

Hey Matt,

I tried turning this into a straight table instead of pivot so we could have totals but i'm having some issues. As you can see in the screen shot below the Sales column has a total of 1350 but it should show 1265. Any ideas?

[#5]

Never mind, just realized there is a sum of rows option. Thanks again!