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

Combined Pivot Bar and Line Graph Not Maintaining Format

Kevinsid

New Member
I am trying to represent a combined bar and line graph, showing revenue as the bar graph and each expense type as a line graph. The idea to see where expenses visually are above revenue, the problem is when i try and filter on the pivot or via the slicer the expense type the entire graph returns to a bar graph as per Graph 2.

Please help and advise how i overcome this problem.
 

Attachments

  • Combined-Bar+line Graph Query.xlsx
    135.2 KB · Views: 2
This is the 3rd time I'm coming across this sort of thingh in th last 4 weeks or so.
See https://www.excelguru.ca/forums/showthread.php?7879-Pivot-chart-combined-does-not-keep

I'll do a little coding for you in the same vein, but could you confirm that all the series will be line charts except for the Revenue which will be a bar/histogram (when it's showing)? I'm thinking along the lines of having the whole chart's type be line, and only switching the Revenue to a bar chart if it's showing.
 
Yes, the only series that will be bar/histogram will be revenue. Is the only way to overcome this by using VBA coding? I would think that the pivot table graph should be able to overcome this within the normal functions/tools of pivot table/pivot charts??
 
Is the only way to overcome this by using VBA coding? I would think that the pivot table graph should be able to overcome this within the normal functions/tools of pivot table/pivot charts??
I haven't found a way to do this any other way (apart from manually resetting the chart, that is), although I'm no expert. I agree with you that this behaviour is annoying - and the settings for a particular series should be retained. (I'm using Excel 2010 - I don't know whether there have been improvements/fixes in later versions.)
In the attached there's this snippet of code in the appropriate sheet's code-module:
Code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
With ChartObjects("Chart 7").Chart
  .ChartType = xlLine
  With .SeriesCollection("Revenue")
    .ChartType = xlColumnClustered
    .Format.Fill.ForeColor = 5880731  'light green
  End With
End With
End Sub
 

Attachments

  • Chandoo34619Combined-Bar+line Graph Query.xlsm
    142.7 KB · Views: 1
I use a Pattern Fill
ie: 90% Green with 10% white dots and 90% Red with 10% white dots
It seems to not upset the charts under that condition

ps: I believe this post is cross-posted on another site
It is generally considered good practice to not cross post or if you do let people know

please read the site rules at:
http://forum.chandoo.org/link-forums/new-users-please-read.17/
 
Back
Top