Bar and Average Line Chart from Pivot Table
- Thread starter MrsLisa
- Start date
Chihiro
Excel Ninja
Read through link for how to do this.
https://www.extendoffice.com/docume...pivot-chart-add-average-grand-total-line.html
However, do note that it isn't dynamic (i.e. even if you filter for an attribute, avg will remain avg of overall).
If it needs to be dynamically adjust with filter selection. You'll probably need VBA to link table/calculation with filter selection on Pivot.
https://www.extendoffice.com/docume...pivot-chart-add-average-grand-total-line.html
However, do note that it isn't dynamic (i.e. even if you filter for an attribute, avg will remain avg of overall).
If it needs to be dynamically adjust with filter selection. You'll probably need VBA to link table/calculation with filter selection on Pivot.
p45cal
Well-Known Member
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:
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:
p45cal
Well-Known Member
Chihiro, I was actually asking MrsLisa about her/his file's picture:
I notice at the bottom that the legend for the 'line' looks like a line's legend.
This is what my legend looks like:
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.
I notice at the bottom that the legend for the 'line' looks like a line's legend.
This is what my legend looks like:
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:
Chihiro
Excel Ninja
Yep.
Linear(Avg) this is standard naming convention for linear trendline. It's based on sample I provided below. Format of trendline was adjusted to solid red line.
http://chandoo.org/forum/threads/using-dynamic-tables-average-for-a-chart.30563/#post-182255
Linear(Avg) this is standard naming convention for linear trendline. It's based on sample I provided below. Format of trendline was adjusted to solid red line.
http://chandoo.org/forum/threads/using-dynamic-tables-average-for-a-chart.30563/#post-182255
p45cal
Well-Known Member
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…
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…
MrsLisa
New Member
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
Well-Known Member
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
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
MrsLisa
New Member
Gotcha. I'm going to go and study what you did. Thank you.