• 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.

Bar and Average Line Chart from Pivot Table

MrsLisa

New Member
Hello. If possible, could someone guide me on how to create a bar with average line from a pivot table. I have it arranged for a table but I have to organize it in a pivot table because I have too much data. Your help is appreciated. Thank you.
 

Attachments

  • Sample 2.xlsx
    146.4 KB · Views: 14
Thank you as always Chihiro. This has been problem in that I need it to be dynamic. I will do a search for the vba link table calculation tutorial. Thank you.
 
When I looked at this, I didn't expect it to be difficult:
While I know a bit about pivot tables I found it difficult to get a calculated item/field to return an average of the visible/filtered items; if someone knows how to do this then this simplifies things a lot.

I have managed to do it but I think I'm going about it in a convoluted way, so in case there's a simpler answer, can you tell me how you got the red average line on your picture of a chart in your file (my Excel has difficulty putting a line chart on its side, so combining it with a barchart is good - is it something after Excel 2010?)?


This is what I have so far:
upload_2016-8-3_15-56-26.png
 
It's actually invisible (transparent) series of averages that's set to 100% overlap with other bar chart series.

Then trend line is added to transparent series to show average line.
 
Chihiro, I was actually asking MrsLisa about her/his file's picture:
upload_2016-8-3_16-8-42.png
I notice at the bottom that the legend for the 'line' looks like a line's legend.
This is what my legend looks like:
upload_2016-8-3_16-16-54.png
Do you know this for certain?
I get my red line from a stacked barchart on a secondary axis, with the left bar being transparent (average) and the right (stacked) bar being v.small with no fill and red outline.
 
Last edited:
MrsLisa,
in the attached are 2 new pivot tables (yours seems to be linked to external data so I left it alone) linked by a couple of slicers so that Attribute and Brand are synchronised.
There's a pivot chart of the first pivot, the second pivot is just to provide an average value for the first.
I altered your raw data to include multiple values for Attribute1/Brands (I notice that I needn't have - Attribute 8 already has this), the reason being wanting to know how you want to handle these; All Attributes apart from 1 and 8 in the attached look right in the chart (the average looks like it's the average of the bars) but for 1 and 8 they look awry, as do averages when multiple Attributes are selected in the pivot.
See if you want to go forward with this…
 

Attachments

  • chandoo30597Sample 2.xlsm
    164.8 KB · Views: 61
OMG Pascal its working. It is actually working!!!!! This is fantastic. Thank you so much!!! And also thanks to everyone for sharing your time and efforts. Your are truly the best group. Chihiro I also cannot thank you enough. You've seen me struggle through many versions of this lol. But isn't I've learned so much. Its not that I want you to do it for me, but after I struggle to and research it is good to know that you are all here to help and teach. Have a great evening everyone.
 
Last edited:
P45cal I'm stumped. How did you get the sum of average score .8315 to fill the as a column in your first pivot table?
 
There is a calculated field in the pivots called aver. When one of the pivot tables gets updated/changed there is a line of code (
pt.CalculatedFields("aver").StandardFormula = Evaluate("GETPIVOTDATA(""Average of Score""," & Target.TableRange1.Cells(1).Address(0, 0, , 1) & ")") 'tweak x 2) which sets the value from Grand Total of the Average of Score at the bottom of PivotTable2.
That field is included in PivotTable1
 
There is a calculated field in the pivots called aver. When one of the pivot tables gets updated/changed there is a line of code (
pt.CalculatedFields("aver").StandardFormula = Evaluate("GETPIVOTDATA(""Average of Score""," & Target.TableRange1.Cells(1).Address(0, 0, , 1) & ")") 'tweak x 2) which sets the value from Grand Total of the Average of Score at the bottom of PivotTable2.
That field is included in PivotTable1
Gotcha. I'm going to go and study what you did. Thank you.
 
Back
Top