pivot totals (is it too difficult ?)

[sunseeker]

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

[Mark W.]

How is your data list organized?

[sunseeker]

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

[lenze]

What happens when you move Status to the Row Field after products?

[ This Message was edited by: lenze on 2002-04-04 09:32 ]

[Mark W.]

Instead of organizing your data like…

*

Month	Product	Status	Total Sales
Jan	A	Result	10
Jan	A	Budget	25
Feb	B	Result	40
Feb	B	Budget	30

…organize it like…

*

Month	Product	Budgeted Sales	Actual Sales
Jan	A	25	10
Feb	B	30	40

…and, create a PivotTable like…

*

*	Month	Data	*	*	*	*	*	*	*
*	Jan	*	*	Feb	*	*	Total Budgeted	Total Actual	Total Delta
Product	Budgeted	Actual	Delta	Budgeted	Actual	Delta	*	*	*
A	25	10	-15	*	*	*	25	10	-15
B	*	*	*	30	40	+10	30	40	+10
Grand Total	25	10	-15	30	40	+10	55	50	-5

*
...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 ]

[Ian Mac]

On 2002-04-04 16:31, Mark W. wrote:
Instead of organizing your data like…

*

…organize it like…

*

…and, create a PivotTable like…

*
*


[ 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 ]

[Mark W.]

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

HTML

[NateO]

Click on edit and you shall see the light. You may be sorry you asked!