Look closely at the results shown in Figure 12.27. The calculation for DIVISION1 is correct. Division1 is a total of ABC + DEF. Some quick math confirms that 46 million + 47 million is about 93 million. However, the Grand Total should be 93 million + 53 million, or about 146 million. Instead, Excel gives you a grand total of $241 million. The total revenue for the company just increased by almost $100 million. Excel clearly whacks out and doesn't know how to deal with calculated items added to a field. The only plausible method for dealing with this is to attempt to hide the products that make up Division1. The results are shown in Figure 12.28:

With PT.PivotFields("Product") .PivotItems("ABC").Visible .PivotItems("DEF").Visible End With

False False

Figure 12.28

After the components that make up the calculated Division! item are hidden,the total revenue for the company is again correct. However, it would be easier to add a new field to the original data with a Division field.

0 0

Post a comment