Powerpivot calculated field question

[edusuro]

Hi all,

I am currently stuck trying to figure out a formula to obtain the desired results in a calculated field in excel. Basically, I my structure is similar to below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Inventory[/TD]
[TD]Restock level[/TD]
[TD]Replenish Qty.[/TD]
[/TR]
[TR]
[TD]Group 1[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] Line 1[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] Line 2[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD] Line 3[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

The formula for replenish qty currently is:
=IF(([Restock level]-SUM([Inventory]))<0,0,([Restock level]-SUM([Inventory])))

This formula provides me with the number I need at the item level. However, for the total, I need the pivot table to show the sum of all replenish qty from every line, in this case 1. Instead the formula performs the same calculation at the aggregate level which gives me 0.

Does anyone know a way I can bypass this and have it show the actual sum of that field?

THANKS!