Help with a Combining a Visualization on 2 Pivot Tables

How would I accomplish combining the two pivot charts into one visualization? Basically, stack the Line chart on top of the bar chart which has the horizontal axis in the same order as the Bar Chart. The line chart is a Running Total of the vertical axis in the Bar Chart.
CombineTwoPivots.png
LVL 1
-PolakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Polak,

Are you giving inputs of these pivot from a range or how you are determining these inputs?? Also it will be easy if you can give me your sample workbook as i don't want to create a dummy data of my own to write this macro for you..

Saurabh...
-PolakAuthor Commented:
I will create some dummy data for you a little later, the pivot is looking at a table for the data.
Saurabh Singh TeotiaCommented:
When you create this data..can you also create it along with the table that you are referring to..
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

A pivot chart does not support a combination of column and line. You will need to create a regular chart for that.

Click an empty cell, insert a new column chart. Edit its data source and select the series from the High/Medium/Low pivot table.
Then add the Sum-of-Hours pivot table values as another series. Format that series to be a line chart.

When the pivot tables get updated, you may need to adjust the chart data source range to cater for varying number of rows.

cheers, teylyn
-PolakAuthor Commented:
Humm, Saurabh do you agree? The charts need to remain in pivot so they can be sliced and update dynamically as the data in the table changes.

Is there perhaps an analysis visualization addin/app/pack that I could purchase (or freeware) that can do this with tabled data (or two pivoted series). Seems silly that I can't visualize something so simple as pretty much a cumulative trendline.
Saurabh Singh TeotiaCommented:
From my end what i thought that rather then charts you want to play with pivot that when you change the data tables..you want both the pivots show the same value..We can do that..and then the same pivot can be defined to use chart range and again we can try defining normal chart if you want both column and line as i was more focused on pivots first..
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Having both pivots show the same values is easily achieved without any VBA by using slicers.

The question clearly states that the desired result is "combining the two pivot charts into one visualization".

Pivot charts don't support different data sources.  You need to create regular charts for that. You will find this approach used widely by Excel experts. You can make this approach dynamic by basing the chart on dynamic ranges that will grow and shrink with the pivot table.  

The attached file has an example for this. Add more data to the table, refresh the pivot tables and see the chart update.

cheers, teylyn
dynamicPivotCombinationChart.xlsx
Rory ArchibaldCommented:
A pivot chart does not support a combination of column and line

Um, yes it does. The problem here is that you can't show just one running total - you'd have to show it for each of the column items. Unless you can use Power Pivot so you can create a set.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Ah, you're right, Rory. I was having a 'moment'. There are quite a few limitations with Pivot charts, though, and some charts cannot be done with Pivot Charts at all, like Scatter, bubble or stock charts. But column/line combinations are doable.

My suggestion for the combined visualisation still stands, though. Since the running total is in a different pivot table,
-PolakAuthor Commented:
Teylyn, your solution works perfectly, see attached. However, I'm having trouble recreating it.....

As soon as I attempt to enter in the range for the High, Med, and Low series (PivotTable1) it converts my regular chart to a pivot chart as soon as I press enter. How are you selecting the range so that it doesn't auto-convert to a pivot chart upon selecting pivoted data?

Also, could the range be written with a if/index/match function and "ZZZZZZZZ" (or simply 1048576) to grow / shrink if data exists in the pivot table?
dynamicPivotCombinationChart.xlsx
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
The range names for the chart are dynamic and will grow and shrink. The basis is the chtLabels name with the formula

=OFFSET(Sheet1!$F$4,0,0,COUNTA(Sheet1!$F:$F)-2,1)

If the data in the first column is text, you could use an Index/Match combo, if you prefer that:

=Sheet1!$F$4:INDEX(Sheet1!$F:$F,MATCH("zzzz",Sheet1!$F:$F,1))

To create the chart, I applied these steps:

- click an empty cell not in any table
- insert a column chart, which will be empty
- edit the data source for the column chart and manually add a series using the formula
  =Sheet1!chtHigh
 Note how the sheet name precedes the range name. You must enter the sheet name or the file name before the range name. Whatever is easier to type. Excel will turn it into the file name when you close the dialog.
- edit the Axis Labels and enter =Sheet1!chtLabels
- add the the other three series
- format the hours series to be on the secondary axis.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
-PolakAuthor Commented:
Right on, got it now, didn't open up the name manager before. Thanks for explaining!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.