Pivot chart combined - does not keep

B

boris

New member
Joined
Jun 1, 2017
Messages
4
Reaction score
0
Points
0
Hello, I have following problem:

Pivot chart combined - one category has "line" type and other have column stacked. it is displayed exactly in way I need.

BUT: When I filter-out one value from the chart, the format of the chart does not keep.
It is obvious from attached file:View attachment Pivot_chart.xlsx
If I uncheck Peter from the pivot chart (or pivot table) the whole chart is column stacked now.

Could you please help? How to do to the chart keeps its format?
Thanks.
 
boris said:
Hello, I have following problem:

Pivot chart combined - one category has "line" type and other have column stacked. it is displayed exactly in way I need.

BUT: When I filter-out one value from the chart, the format of the chart does not keep.
It is obvious from attached file:View attachment 6906
If I uncheck Peter from the pivot chart (or pivot table) the whole chart is column stacked now.

Could you please help? How to do to the chart keeps its format?
Thanks.
Click to expand...

Boris, Not quite sure what you exactly need here. (If you can explain your end game and what the data means, can build you something) Check this out and suggest adjustments and will flex it. The State and name are visual filters, play with them!

https://www.screencast.com/t/ZBIM8AhP7G
 

Attachments

  • Pivot_chart.xlsx
    32.6 KB · Views: 41
Last edited:
Ed Kelly, thanks for your response.
I try to explain it better:
1) Data are only for ilustration, they have no real sense. In reality, of course, I have reasonable data.
2) I need COMBINED chart:
a) Peter-yes, Peter-no, Michael-yes etc. in COLUMNS
b) and Limit as a LINE - like in my attached file.
3) Problem arises when I want to hide for example Peter. Then the chart is not combined any more. The Limit LINE changes into COLUMN. WHY???
(Action in point 3 above will be done by other users for whom I make the chart. But due to described behaviour it is unusable:-( )
 
I came across this problem very recently, my suggestion in this case is to have some event code to handle pivot table updates (this code looks for the series name beginning with 'Limit' and makes sure it's a line chart every time):
Code: 
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
For Each sery In ChartObjects("Graf 1").Chart.SeriesCollection
  If sery.Name Like "Limit*" Then
    sery.ChartType = xlLine
    'sery.Format.Line.ForeColor.RGB = RGB(255, 0, 0) 'red line
    Exit For
  End If
Next sery
End Sub
There's lot's of hard-coding in there but it seems to work (see attachment). The code is in the sheet called Table's code-module. There's also a commented-out line to turn the line red, should you need it.
 

Attachments

  • ExcelGuru7879Pivot_chart.xlsm
    28.1 KB · Views: 40
Thanks a lot. I try it in my work on Monday:)
 
You must log in or register to reply here.
Back
Top