G
Guest
I have created a pivot table based on this sample data;
Type1 Type2 Version Cost
Fruit Apple Actual 2
Fruit Banana Actual 2
Fruit Apple Forecast 5
Fruit Banana Forecast 5
Car BMW Actual 10
Car HONDA Actual 10
Car BMW Forecast 3
Car HONDA Forecast 3
I then added a Calculated Item in my pivot table to calculate a variance
between actual and forecast (ie. Actual less Forecast). What my issues are;
1. Why my pivot table is showing a calculation for every single 'Type2' data
regardless of 'Type1' grouping - see below? I did not expected to see a line
under CAR & APPLE or Fruit & BMW?????
2. Is there a solution around this?
Sum of Cost Version
Type1 Type2 Actual Forecast Variance
Car Apple 0
Banana 0
BMW 10 3 7
HONDA 10 3 7
Car Total 20 6 14
Fruit Apple 2 5 -3
Banana 2 5 -3
BMW 0
HONDA 0
Fruit Total 4 10 -6
Grand Total 24 16 8
Type1 Type2 Version Cost
Fruit Apple Actual 2
Fruit Banana Actual 2
Fruit Apple Forecast 5
Fruit Banana Forecast 5
Car BMW Actual 10
Car HONDA Actual 10
Car BMW Forecast 3
Car HONDA Forecast 3
I then added a Calculated Item in my pivot table to calculate a variance
between actual and forecast (ie. Actual less Forecast). What my issues are;
1. Why my pivot table is showing a calculation for every single 'Type2' data
regardless of 'Type1' grouping - see below? I did not expected to see a line
under CAR & APPLE or Fruit & BMW?????
2. Is there a solution around this?
Sum of Cost Version
Type1 Type2 Actual Forecast Variance
Car Apple 0
Banana 0
BMW 10 3 7
HONDA 10 3 7
Car Total 20 6 14
Fruit Apple 2 5 -3
Banana 2 5 -3
BMW 0
HONDA 0
Fruit Total 4 10 -6
Grand Total 24 16 8