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

On Jun 20, 10:20 pm, Ed wrote:
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

Check the field settings on the type2 field and uncheck show items
with no data

Thank you but unfortunately it did not help. Are there any other solutions?

" wrote:

On Jun 20, 10:20 pm, Ed wrote:
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

Check the field settings on the type2 field and uncheck show items
with no data