Search code examples
excelchartspivot-tableexcel-chartsvba

custom chart data labels based on raw number not %

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.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 ()

Solution

  • 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