Pivot table chart problem, How to show different subtotal levels
in [Charts]
|
Prev: Stacked and Single Column on the same axis
Next: Pivot table & chart problem, How to show different subtotal levels
From: Ycorth on 9 Mar 2010 03:04 Hi, Using Excel 2003, I have a pivot table using external data via ODBC. I have a chart based on my pivot table. The table and chart are working correctly. The PTcontains two levels of sales subtotals 1st level is by county 2nd level is by state. My client is telling me that the data in the chart is too detailed. He wants to have the both levels of subtotals in the PT, but only the county subtotals in the chart. If I delete the county sales subtotals from the chart, they are also deleted from the PT. Is it possible for the chart to be based on the PT and not show the same subtotal levels ? --- I used to have a workaround for this where I hid a second PT underneath the chart. This second PT was based on the same external ODBC data but did not have the county subtotal details. One satisfied client. And that was great while it lasted ... The problem now is that the client needs to use the data filters in the visible PT and his filtering is not being reflected in the second PT and therefore not being updated on the chart. So I have tried basing the second table on the first table hoping to catch any filtering but that is not working. Maybe I'm doing it wrong but the filtering in the first PT is not having any effect on the chart. Any help appreciated. TIA, Ycorth
From: AdamV on 12 Mar 2010 08:36
You can use VBA to twiddle the filters, usually based on other controls such as combo boxes (choose something in a combo box or data validation list, this triggers the macro, which sets filters on multiple PTs) Or you can upgrade to Excel 2010 (due in May / June) and use slicers, which are designed to do exactly what you need here - provide easy to use filtering that you can link to multiple pivot tables if you wish. Hope this helps Adam On 09/03/2010 08:04, Ycorth wrote: > Hi, > Using Excel 2003, > I have a pivot table using external data via ODBC. > I have a chart based on my pivot table. > The table and chart are working correctly. > > The PTcontains two levels of sales subtotals > 1st level is by county > 2nd level is by state. > > My client is telling me that the data in the chart is too detailed. > > He wants to have the both levels of subtotals in the PT, > but only the county subtotals in the chart. > If I delete the county sales subtotals from the chart, they are also deleted > from the PT. > > Is it possible for the chart to be based on the PT and not show the same > subtotal levels ? > > --- > I used to have a workaround for this where I hid a second PT underneath the > chart. > This second PT was based on the same external ODBC data but did not have the > county subtotal details. > One satisfied client. > And that was great while it lasted ... > > The problem now is that the client needs to use the data filters in the > visible PT > and his filtering is not being reflected in the second PT and therefore not > being updated on the chart. > > So I have tried basing the second table on the first table hoping to catch > any filtering but that is not working. > Maybe I'm doing it wrong but the filtering in the first PT is not having any > effect on the chart. > > Any help appreciated. > TIA, > Ycorth > > |