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?
Harry LeeConnect With a Mentor Commented:

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.