Calculated Fields in Chart

**ruchi** · 03-13-2009, 02:12 PM

Hi All!

I have created a pivot table with two data fields and a calculated field. However, when I try to create a chart from that pivot table, somehow the calculated field does not shows up in chart. I am not sure what am i doing wrong. Till now, i have not tried writing a code, i was just trying to insert a chart so that i could create a combination chart.

Could somebody please help me!!

Code:

 ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
                SourceData:="'Temp'!R1C1:R" & FinalRow & "C" & FinalCol, _
                TableName:="PivotTable2"

    ' Turn off updating while building the table
    'PT.ManualUpdate = True

    ' Set up the row & column fields
    
    ActiveSheet.PivotTables("PivotTable2").CalculatedFields.Add Name:= _
    "Average", Formula:="=Customer Profit/Revenue"

    With ActiveSheet.PivotTables("PivotTable2")
    
        .PivotFields("Project Desc").Orientation = xlPageField
        .PivotFields("Project Desc").Position = 1
        
        .PivotFields("As Of Data").Orientation = xlRowField
        .PivotFields("As Of Data").Position = 1

        With .PivotFields("Revenue")
            .Orientation = xlDataField
            .Position = 1
            .NumberFormat = "#,##0_);(#,##0)"
        End With

        With .PivotFields("Customer Profit")
            .Orientation = xlDataField
            .Position = 2
            .NumberFormat = "#,##0_);(#,##0)"
        End With
        
        With .PivotFields("Average")
            .Orientation = xlDataField
            .Position = 3
            .NumberFormat = "#0.00%"
            .Name = "Profitability"
        End With

    End With
    
    With ActiveSheet.PivotTables("PivotTable2")
        
        .RowGrand = False
        .ColumnGrand = False
        
        .DataFields("Sum of Revenue").Name = "Revenue "
        .DataFields("Sum of Customer Profit").Name = "Cust Profit"
        
        .DisplayErrorString = True
        .ErrorString = "-"
    
    End With

**Andy Pope** · 03-14-2009, 09:09 AM

Does the field display in the chart when done manually?

Can you post example workbook.

**ruchi** · 03-14-2009, 12:01 PM

No, the field does not get displayed even when i try to insert a chart manually.

Could you please help me?

**Andy Pope** · 03-14-2009, 12:09 PM

So if the field does not show up normally what makes up think a coded solution can make it appear?

I think you may need to create a normal chart based on the pivot table.
http://peltiertech.com/WordPress/reg...-pivot-tables/

**ruchi** · 03-14-2009, 02:53 PM

I know, that much i realized. That is why i did not take the trouble of coding it. I have already been through that article you have mentioned.
My question is why does this happen? Is it because I am trying to show percentage value with other two data fields which are not %. Though I am almost sure, that is not the reason because I tried to display only that data field in a chart, but still that value did not appear. I am not sure what to do next.

What do you think are the options that i have now to display it?

**ruchi** · 03-14-2009, 04:05 PM

I just recorded a macro and found this code, which plotted the chart atleast for one month!

But when i try to use the code to achieve for the entire pivot, it again does not plot the third data field! Plus it gives an error for the formatting part.. Please help..

Code:

    Charts.Add
    ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Line - Column on 2 Axes"
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("C17")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!R10C5"
    ActiveChart.SeriesCollection(1).Name = "=Sheet1!R10C4"
    ActiveChart.SeriesCollection(2).Values = "=Sheet1!R11C5"
    ActiveChart.SeriesCollection(2).Name = "=Sheet1!R11C4"
    ActiveChart.SeriesCollection(3).Values = "=Sheet1!R12C5"
    ActiveChart.SeriesCollection(3).Name = "=Sheet1!R12C4"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
        .Axes(xlCategory, xlSecondary).HasTitle = False
        .Axes(xlValue, xlSecondary).HasTitle = False
    End With

**Andy Pope** · 03-15-2009, 08:36 AM

It could be that calculated fields are not included in the pivot chart.

It's impossible to tell whether you can create the chart any other way as we don't know what the source data layout looks like or the chart you are trying to create.

Can you post example workbook detailing what you are trying to do.