Please see the attached example. I am trying to subtotal the Opportunity values by each Supplier_Proposal. I have selected the Partial Sums check box and Expand all options as other discussions have suggested without success. Can total Opportunity be isolated by Supplier_Proposal in this Pivot or a Bar chart? A straight table allows summation by all Suppliers_Proposal, but this does not do me any good. Any suggestions are welcome.
Thanks John, the above got me part of the way there in that I can now calculate the subtotal of each supplier as shown in the updated example attached.
My next issue is that the the subtotals do not recognize my IF modifier that zero's out negative values. The reason I need this function to work is that negative numbers do not present an 'Opportunity' and therefore should not be calculated against the subtotal/total Opportunity for that Supplier.
For example, the Opportunity values for Supplier_Proposal 'T' are -8, 27, 18 which subtotal correctly to 37. If I zero out -8 with an IF statement, however, the total remains 37 instead of updating to the sum total of 0, 27, 18. The new value should then be 45, but it remains at 37.
I performed the same IF on the BM expression to zero out that value for Supplier_Proposal 'T' to test and the same thing occurs where the sum of 0, 45, and 20 is incorrectly shown to be 77 rather than 65.