Pivot Table % on subtotals 1

[ReineM]

Hello...the following pivot table works great for one month but now I want to add data each month. I need to see all months on the same report for comparison. The problem is that when I have more than one month the % of total is based on grand total rather each months totals. Is there a way to have the percentages calculate for each month? Is it possible to have running year-to-date totals and percentages? Also...can I add each month to an existing pivot table rather than recreating the table every month with the new data?

Thanks for the help...I've just discovered pivot tables and have been struggling with this for days!

[b]
Date    Type        Area     Source        Total   %of Total
[/b]
[u]
Jan                                        249     100% [/u]
[u]
        Surgery1                            49      20% [/u]
                   [b] GP                      13       9%[/b]
[i]                             Friend          3       1%
                             Print           2       1%
                             Radio           7       3%
                             Unknown         1       0%[/i]
                   [b] Med                     18       7%
                    Red                     13       5%
                    Other                    5       2%[/b]
[u]
        Surgery2                           200      80%[/u]
                [b]    GP                      22       9%[/b]
[i]                             Doctor          6       2%
                             Friend          5       2%
                             Print           1       0%
                             Radio           5       2%
                             Staff           3       1%
                             Unknown         1       0%
                             Yel Pgs         1       0%[/i]
            [b]        Med                     67      27$
                    Red                     65      26%
                    Other                   46      18%[/b]
[u]
Grand Total                                249     100%[/u]

 

[SkipVought]

Hi,

I'm afraid that I can't be much help on the % question. I think that you're stuck with % of Total.

However, on the matter of adding to the source data table, check out How can I rename a table as it changes size faq68-1331, using the OFFSET function to make the table range name DYNAMIC.

Then, all you have to do is refresh the table. In fact you can use this little VBA procedure to "automatically" refresh the PT assuming that your PT and source data are on separate sheets (which is technically prudent)

Private Sub Worksheet_Activate()
   Dim pvt As PivotTable
   For Each pvt In ActiveSheet.PivotTables
       pvt.PivotCache.Refresh
   Next
End Sub

Copy this code, right click the sheet tab containing the pivot table, select View Code and paste in the code window.


Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[ReineM]

Hi skip,

Thanks for the refresh tip! I can see that that part is working. BUT...I cannot seem to make the OFFSET function work in either VBA or Insert-Name-Define. When I use the latter it seems to be working because when I click in the formula I can see that it is highlighting the correct columns and the rows go forever, but it does not update my pivot table. Am I missing a step? Do I need to do something more in my pivot table? I am not great at deciphering code so it's possible I am not entering it correctly...

 

[Gavona]

If you made Date a column Field rather than a row field would that do it for you?

Thanks,

Gavin

 

[SkipVought]

The OFFSET spreadsheet function is NOT the same as the Offset Property in Excel VBA.

Use the OFFSET function in Insert/Name/Define. Enter the range name in the Names in workbook textbox and the OFFSET function in the refers to textbox.

Skip,
[sub]
[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken! [/sub]

 

[ReineM]

Gavona - no, that doesn't work either, it still gives me a percent of grand total.

SKip - I never did figure out what I was doing wrong, however, when I created a new pivot table after doing insert/name/define, it works perfectly!

Thanks, everyone, for your help. If you run across a solution to my % of total problem, let me know!

Reine