pivot totals (is it too difficult ?)

sunseeker

New Member
Joined
Mar 31, 2002
Messages
5
using a pivot table i want to compare actual results and budgeted figures for a half years period in a monthly split.
line 1 gives the month.
line 2 gives result in 1st column and budgeted amount in 2nd column for the relevant month.
my question: the total given automatically with the pivot table at the righthand side adds up all figures from line 2,actual figures and budget figures (which makes no sense in this case). I'd like to see a total for all result figures and a total for all budget figures.
how is that possible ?
thanks
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
On 2002-04-03 07:57, Mark W. wrote:
How is your data list organized?

In the base table I have four columns with 1.month 2.product 3.Status (Result or Budget) 4.Total sales.
-----
In the pivot table:
Row: product
Column: Month, status (in that order)
Data: Sales

Mark,
does this help ?
Thanks a lot,
regards from Düsseldorf, Germany
 
Upvote 0
What happens when you move Status to the Row Field after products?
This message was edited by lenze on 2002-04-04 09:32
 
Upvote 0
Instead of organizing your data like…<html><body> <table BORDER=3 CELLSPACING=3 CELLPADDING=3><tr><td>Month</td><td>Product</td><td>Status</td><td>Total Sales</td></tr><tr><td>Jan</td><td>A</td><td>Result</td><td>10</td></tr><tr><td>Jan</td><td>A</td><td>Budget</td><td>25</td></tr><tr><td>Feb</td><td>B</td><td>Result</td><td>40</td></tr><tr><td>Feb</td><td>B</td><td>Budget</td><td>30</td></tr></table></body></html>

…organize it like…<html><body> <table BORDER=3 CELLSPACING=3 CELLPADDING=3 ><tr><td>Month</td><td>Product</td><td>Budgeted Sales</td><td>Actual Sales</td></tr><tr><td>Jan</td><td>A</td><td>25</td><td>10</td></tr><tr><td>Feb</td><td>B</td><td>30</td><td>40</td></tr></table></body></html>

…and, create a PivotTable like…<html><body> <table BORDER=3 CELLSPACING=3 CELLPADDING=3 ><tr><td> </td><td>Month</td><td>Data</td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td><td> </td></tr><tr><td> </td><td>Jan</td><td> </td><td> </td><td>Feb</td><td> </td><td> </td><td>Total Budgeted</td><td>Total Actual</td><td>Total Delta</td></tr><tr><td>Product</td><td>Budgeted</td><td>Actual</td><td>Delta</td><td>Budgeted</td><td>Actual</td><td>Delta</td><td> </td><td> </td><td> </td></tr><tr><td>A</td><td>25</td><td>10</td><td>-15</td><td> </td><td> </td><td> </td><td>25</td><td>10</td><td>-15</td></tr><tr><td>B</td><td> </td><td> </td><td> </td><td>30</td><td>40</td><td>+10</td><td>30</td><td>40</td><td>+10</td></tr><tr><td>Grand Total</td><td>25</td><td>10</td><td>-15</td><td>30</td><td>40</td><td>+10</td><td>55</td><td>50</td><td>-5</td></tr></table>
 </body></html>
...Where...

'Budgeted' is Sum of 'Budgeted Sales'
'Actual' is Sum of 'Actual Sales'
'Delta' is a Calculated Field using the formula, 'Actual Sales'-'Budgeted Sales'

'Delta' is formatted as +0;-0;
This message was edited by Mark W. on 2002-04-04 16:56
 
Upvote 0
On 2002-04-04 16:31, Mark W. wrote:
Instead of organizing your data like…<html><body> <table BORDER=3 CELLSPACING=3 CELLPADDING=3 ><tr><td>Month</td><td>Product</td><td>Status</td><td>Total Sales</td></tr><tr><td>Jan</td><td>A</td><td>Result</td><td>10</td></tr><tr><td>Jan</td><td>A</td><td>Budget</td><td>25</td></tr><tr><td>Feb</td><td>B</td><td>Result</td><td>40</td></tr><tr><td>Feb</td><td>B</td><td>Budget</td><td>30</td></tr></table></body></html>

…organize it like…<html><body> <table BORDER=3 CELLSPACING=3 CELLPADDING=3 ><tr><td>Month</td><td>Product</td><td>Budgeted Sales</td><td>Actual Sales</td></tr><tr><td>Jan</td><td>A</td><td>25</td><td>10</td></tr><tr><td>Feb</td><td>B</td><td>30</td><td>40</td></tr></table></body></html>

…and, create a PivotTable like…<html><body> <table BORDER=3 CELLSPACING=3 CELLPADDING=3 ><tr><td></td><td>Month</td><td>Data</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td></td><td>Jan</td><td></td><td></td><td>Feb</td><td></td><td></td><td>Total Budged</td><td>Total Actual</td><td>Total Delta</td></tr><tr><td>Product</td><td>Budged</td><td>Actual</td><td>Delta</td><td>Budged</td><td>Actual</td><td>Delta</td><td></td><td></td><td></td></tr><tr><td>A</td><td>25</td><td>10</td><td>-15</td><td></td><td></td><td></td><td>25</td><td>10</td><td>-15</td></tr><tr><td>B</td><td></td><td></td><td></td><td>30</td><td>40</td><td>+10</td><td>30</td><td>40</td><td>+10</td></tr><tr><td>Grand Total</td><td>25</td><td>10</td><td>-15</td><td>30</td><td>40</td><td>+10</td><td>55</td><td>50</td><td>-5</td></tr></table>
 </body></html>
This message was edited by Mark W. on 2002-04-04 16:35

May I ask how you put this on the board, mark??

_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-04-04 16:39
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top