Pivot Table and Pivot Chart

I have one data source. The source contains among other things.... Sales Person, Number of Customers, Number of Policies, and Total Revenue. I want to pivot table & Pivot Chart these items, but I want the Pivot table to contain Policy Count, Customer counts and Sum of Revenue. I want the chart to be the same, but do not want to show Revenue because it skews the chart with policies being in the 100's and revenue being $100K to $1.0M.. We also have month.

Right now I have

Report Filter - Sales Person
Column - Sum Values
Row - Moth
Values - Sum Policy, Sum Customer, Sum Revenue
Michael FranzCFOAsked:
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.

Harry LeeCommented:
Newbi22,

1) Have you try putting the Count of Policy onto a Secondary Axis? This way, the count of policy is not a straight line at the bottom.

Go to your chart, click onto the data points of Count of Policy, Right Click and choose Format Data Series. On the Series Option window, Plot Series on section and choose Secondary Axis. Ok to close window.

Or you can use the Ribbon to do the job. Go to PivotChart Tools, Format, at the very left of Ribbon, from the drop down list, choose Series "Count of Policy". Then click Format Selection. Then in the Series Option window, Plot Series On section, choose Secondary Axis.

If that's not what you like,

2) Have you considered running the Pivot Chart and Pivot Table separately, so that they are not interacting with each other?

Which is, when you create the Chart, the Pivot Table is automatically created at the same time. Create the chart without the Sum of Revenue.

Move the Chart to it's own tab.

Then create another Pivot Table for full detail you want.
0

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
Michael FranzCFOAuthor Commented:
I had to go with option #2. Thanks for the help.
0
Harry LeeCommented:
Glad I can help.
0
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.