PivotTable Grand Totals By Item

[Guest]

(I’m hoping the table format is somewhat preserved in this post, so that the
question is readable...if not, I apologize!)

If I have a table structured like this:

Product Line Jan Feb Mar
Apples Revenue 500 600 600
Apples Profit 50 60 60
Oranges Revenue 400 500 400
Oranges Profit 25 35 25
Pears Revenue 200 200 100
Pears Profit 40 40 20


I can create a Pivot Table like this:


Product Line SumofJan SumofFeb SumofMar
Apples Revenue 500 600 600
Profit 50 60 60
Apples Total 550 660 660
Oranges Revenue 400 500 400
Profit 25 35 25
Oranges Total 425 535 425
Pears Revenue 200 200 100
Profit 40 40 20
Pears Total 240 240 120
Grand Total 1215 1435 1205


But I would also like to have the pivot table include rows near the bottom
for “Grand Total Revenue†and “Grand Total Profit†(rather than giving me the
Grand Total of Revenue + Profit, which isn't meaningful).

Is this possible?

 

[Hans Knudsen]

Right click somewhere in the pivot table and select Table Options. In the Pivot Table Options dialog uncheck Grand totals for rows
and press OK.
Now click Pivot Table on the Pivot Table toolbar and select Formulas and Calculated Field. In the Name field write for example
Total. in the Formula field clear the 0, press Jan in theFields area and press Insert Field, enter a +, press Feb, press Insert
Field, enter again a +, press Mar and press Insert Field and OK.

Hans

 

[Guest]

Thank you for the response.

When I follow those steps, I get a Total column for Jan/Feb/Mar at the far
right of the table. What I'm looking for is a "Grand Total Revenue" and
"Grand Total Profit" at the bottom....so the left side of the PivotTable
would look something like:


Product Line
--------- -----
Apples Revenue
Profit

Oranges Revenue
Profit

Pears Revenue
Profit

Grand Total Revenue
Grand Total Proft


The bottom two lines are the crux of what I'm looking for. I can only seem
to get one Grand Total line that adds both Revenue+Profit, which isn't
meaningful.

 

[Hans Knudsen]

If you are interested (and you give me an e-mail address - can't use the one below) I can send you a workbook.
Hans

 

[oscargiuffrida]

Can you send me a workbokk with the solution to problem.

TIA
Oscar

Hans Knudsen ha escrito: