custom chart data labels based on raw number not %

Question

I have a pivot chart that shows the % of a field as the value, but I would like the label to be a combonation of the % and the ran number the % is based on.

Current:

Current

What I would like:

What I would like

The % number is easy because when I loop through the data seriescollection I can get the values from that, but what I can't figure out is how to get the number in the ().

I can do a countif on the raw dataset (~7000 rows) to get the number, but that would mean I need the current value of the two row axis items.

So is there a way when i'm looping through the seriescollection points to get the value of the two items in the axis fields

E.G. When i'm on the 16.86 point can I get the value of "Responsiveness" and "No Comments / No Response" so I can do the countif and the number in the ()

score 0 · Answer 1 · answered Apr 22 '14 at 18:20

0

You can click on the label, press the = key and then point it to a cell and it will display the value of the cell you point it at. You should make a helper cell to do this that formats the value the way you want it.

answered Apr 22 '14 at 18:20

Bmo

1,212
11
34

Problem is that the pivot table and chart are needing to be updated on the fly, thats why im going vba route. – Chandler R Reeves Apr 22 '14 at 18:52
Reading fail. I've brought shame upon my house. – Bmo Apr 23 '14 at 10:26
No worries, I am thinking that a loop from 1 to number of points and then try to get data from the XValues in the seriescollection might work, but I can't get data from the XValues to read correctly. :( – Chandler R Reeves Apr 23 '14 at 15:01
Pivots are funny things to retrive data from at times. Do you have to use a PT? Can you set up `sumif` set of ranges? – Bmo Apr 23 '14 at 15:32

score 0 · Accepted Answer · answered Apr 24 '14 at 21:34

It looks like I got the answer to my own question. I eneded up looping through the pivotfield and skipping the ones that were not needed, then updating the points during the loop.

Here is a sample of what I did:

Set WSpt = Sheets("PivotTablesSheet")
Set pt = WSpt.PivotTables("PivotTableName")
Set pf = pt.PivotFields("FieldName")
F = pt.PivotFields("FilterName").CurrentPage.Name 'if Needed
Vals = Cht.SeriesCollection(1).Values 'Values of the chart because you can not work with them directly :(
Set LR = WSpt.Cells(pf.DataRange.Row, pf.DataRange.Column) 'First Cell in Field DataRange

'loop through cells in FieldName DataRange
For i = 1 To pf.DataRange.Cells.Count
    'Put in check to see if the cell that was currently being checked was part of the primary field or the secondary one (only needed because I had a multi-level field setup
    On Error Resume Next
    tmp = pt.PivotFields("Main").PivotItems(LR.Value)
    If Err.Number = 0 Then
        Q = LR.Value
        Set LR = LR.Offset(1, 0)
        i = i - 1
    Else
        RC = LR.Value
        Set LR = LR.Offset(1, 0)
        'change formula to get the number value based on if the "(All)" option was selected or if just some items in the table need to be counted
        If F = "(All)" Then
            'Save Results of CountIF in the NUM variable
            Num = Application.WorksheetFunction.CountIfs(Sheets("DataSheet").Range(Sheets("DataSheet").ListObjects("DataTBL").ListColumns("Main").DataBodyRange.Address), Q, _
                Sheets("Data").Range(Sheets("DataSheet").ListObjects("DataTBL").ListColumns("FieldName").DataBodyRange.Address), RC)
        Else
            Num = Application.WorksheetFunction.CountIfs(Sheets("Data").Range(Sheets("DataSheet").ListObjects("DataTBL").ListColumns("FilterName").DataBodyRange.Address), F, _
                Sheets("Data").Range(Sheets("DataSheet").ListObjects("DataTBL").ListColumns("Main").DataBodyRange.Address), Q, _
                Sheets("Data").Range(Sheets("DataSheet").ListObjects("DataTBL").ListColumns("FieldName").DataBodyRange.Address), RC)
        End If
        Cht.SeriesCollection(1).Points(i).DataLabel.Text = FormatPercent(Vals(i), 1) & "  (" & Num & ")"
        Cht.SeriesCollection(1).Points(i).DataLabel.Orientation = xlUpward
    End If
Next i
End Sub

custom chart data labels based on raw number not %

2 Answers2