pivot totals (is it too difficult ?)
pivot totals (is it too difficult ?)
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: pivot totals (is it too difficult ?)

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How is your data list organized?

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Instead of organizing your data like…

    ******>
    MonthProductStatusTotal Sales
    JanAResult10
    JanABudget25
    FebBResult40
    FebBBudget30


    …organize it like…

    ******>
    MonthProductBudgeted SalesActual Sales
    JanA2510
    FebB3040


    …and, create a PivotTable like…

    ******>
    MonthData
    Jan Feb Total BudgetedTotal ActualTotal Delta
    ProductBudgetedActualDeltaBudgetedActualDelta
    A2510-15 2510-15
    B 3040+103040+10
    Grand Total2510-153040+105550-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 ]

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    ******>
    MonthProductStatusTotal Sales
    JanAResult10
    JanABudget25
    FebBResult40
    FebBBudget30


    …organize it like…

    ******>
    MonthProductBudgeted SalesActual Sales
    JanA2510
    FebB3040


    …and, create a PivotTable like…

    ******>
    MonthData
    JanFebTotal BudgedTotal ActualTotal Delta
    ProductBudgedActualDeltaBudgedActualDelta
    A2510-152510-15
    B3040+103040+10
    Grand Total2510-153040+105550-5




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

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com