Average Lines on a Pivot Chart

[ScottInTexas]

I have a pivot bar chart showing percentages of events to the total number of events. I want to show a vertical line as an average of one of the event types. Some where in the pivot table I should be able to set the average I want and then some how tell the chart to display this as a line in the chart.

To be more clear. The left side of the chart lists the types of events. Bars strectch to the right to the percentage of that type to the total. The vertical average line would bisect the chart at the average of just the selected event type.

Still not as clear as I would like. But without a picture I can do no better.


Scott

 

[Andrew Poulsom]

I don't think you will be able to do that because a Pivot Chart can have only one chart type, not mixed chart types like a normal chart.

 

[ScottInTexas]

Thanks Andrew,

I would have to use code to create the chart as I do with another. You can do it with something like this;

    Charts.Add
    ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
    ActiveChart.SetSourceData Source:=Sheets("EA Tools").Range("A1:C7")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Tools"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "# Reports vs Events"
    End With
    ActiveChart.HasPivotFields = False

The problem I have is getting a field that shows what I am looking for. I'm not all that clear because it is not all that clear to me.

There weree 96 events for the year. Of those some percentage was from lack of power or some other monitored reason. The cause was from one of four or five causes. What was the average of all events to the one cause called OD?

Thanks for your response.

Scott

 

[Andrew Poulsom]

I see. This shows you how to add a horizontal/vertical line:

http://peltiertech.com/Excel/Charts/AddLineHorzSeries.html

Hope you can adapt it.