• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot chart with with 2 Y axis

Costas

Member
Hi Chandoo,

I have an excel table with sales invoices and I'm trying to create a chart that shows the monthly sales per industry group as sales $ amounts, as number of invoices and as average sales $ per invoice. I created a pivot chart to do this but I can't get the sales $ on the left Y axis and the other two on the right Y axis with different scales on each Y axis. Please see sample file attached.

Thanks
Costas
 

Attachments

  • Sample Sales.xlsx
    163 KB · Views: 9
Hi Costas,

See the enteries sheet chart, not actually pivot chart, I extracted data through formulas and then cretaed this chart.

Regards,
 

Attachments

  • Sample_Sales(1).xlsx
    166.6 KB · Views: 11
Many thanks Somendra!

Is there a way that I can show the Total $ as columns and the other two as lines?
 
@Costas

See the attached.

Changes made: Hide Secondary Axis, Selected it as Lograthmic Scale to move no. of Inv Up.

Regards,
 

Attachments

  • Sample_Sales(1).xlsx
    166.6 KB · Views: 15
Thank you Somendra!

Can I still incorporate the Customer Groups into this chart or would I need to be in pivot charts for this?
 
@Costas

The problem is the no. of invoices are going to be very small figures so the scale will be a problem. I would suggest you to keep $ and $/inv on one axis and as one type and No. of inv to be on secondary axis with a line type.

Kindly suggest & you have to bear one more day for the final discussed solution on this.

Regards,
 
@Costas

I suggest you to keep dollar values in one y-axis (either left for right) and number of invoices on the other, because it is logical to plot similar quantities on same y-axis. no. of invoices is a different one so it can be plotted separately.
 
Hi Somendra,

Given that the values of the $/Inv and No. of Inv are in the hundreds whereas the $ values are in the thousands, isn't it better to have the first two as lines and the other one as columns?

Of course I'll wait another day. Thanks for your help!

Kind regards
Costas
 
Hi Faseeh,

I'd like to keep the 3 series on the same graph but if that is not possible then I'll do the next best thing.

Thanks for your contribution.

Kind regards
Costas
 
Hi Costas,

You third variable # of Invoices are too low in comparison to other. Even other two are going to -ve values sometimes. So please see the attached file with only two variables on different Y axis.

Regards,
 

Attachments

  • Sample_Sales(1).xlsx
    185.4 KB · Views: 6
Hi Somendra,

Thank you very much for the new file. Just to understand a couple of things:
  1. How did you create the "Years" field as I can't find it in the calculated fields?
  2. I checked your pivot table and the source data is the GLEntry table, so how did you get the $/Inv field from the table next to GLEntry to be included in the pivot table?
Thanks
Costas
 
Hi Costas,

Here is the explanation:

Costas said:
How did you create the "Years" field as I can't find it in the calculated fields?
Costas said:
I checked your pivot table and the source data is the GLEntry table, so how did you get the $/Inv field from the table next to GLEntry to be included in the pivot table?

I did nothing new. This was your pivot table only with some tweaks in it.
In your original file you were using text month column of your raw data as row lable in the pivot. Since you had dates in your raw data, I had used those dates in the row Lable of pivot and than group them as Months & Years, to get the required format.

Than you were doing groups in columns I had use them in rows. Than formated the pivot table in tabular form.

Now regarding your $/Inv query, it is your column created I did not created it so, you must dig back how you had done that ;) . All I had found that it is a calculated field.

Regards,
 
You know I've been using pivot tables for so many years and it is the first time that I've come across the Grouping options. Wow! Many thanks for that too Somendra.

Kind regards
Costas
 
Back
Top