Pivot Table refreshing before underlying data can be recalculated

[gooniegirl180]

I have data which shows stock on hand vs sales vs purchase orders by product code, and I've used a pivot table to make the data easy to interpret. I need to be able to show either one branch or all branches, but one of the columns in the pivot needs to show the total on order for that product code for the entire group.

At the moment, I've used a slicer to drive the pivot table filter which in turn adjusts a calculated column in the underlying pivot data - if all branches are selected, then the orders sit against the first instance of a code, but if a branch is selected, then the formula adds up the ordered quantities for each code and displays that total against the selected branch only. This is the (very inefficient) formula I have in the data range:

=IF(AND(Select_Branch="(All)",AP4<>AP3),SUMPRODUCT(--(Code=AP4),($BI$4:$BI$17000)),IF(Select_Branch=AO4,SUMPRODUCT(--(Code=AP4),($BI$4:$BI$17000)),0))
where column AP contains the product codes, AO contains the branch, and BI contains the quantity the branch has on order for that product. The "AND" statement makes sure the total on order only goes against the first instance of each code, as long as All branches have been selected on the Slicer. There is only one instance of each branch/code combination.

However, once the user selects a branch on the slicer, it seems like the pivot refresh happens before the underlying data can recalculate and I have to refresh the pivot table to get the "Group on Order" column showing data. I don't want the user to have to do this, I want the underlying data to recalculate first, then the pivot table refresh happens automatically.

What code can I use to stop the refresh happening until after the data recalculates? I've been on this for three days now and everything I've tried has failed.

Sorry, I'm unable to post the file or details of the data.

Thanks,
GoonieGirl180