Data labels in Pivot Chart when filtered, but no labels when not?

[reacon84]

I have some data spanning 2020 and 2021 as below:

Month	Amount
Jan-20	£200.52
Feb-20	£199.52
Mar-20	£78.55
Apr-20	£55.24
May-20	£187.25
Jun-20	£250.45
Jul-20	£31.98
Aug-20	£36.33
Sep-20	£104.93
Oct-20	£114.07
Nov-20	£61.24
Dec-20	£132.72
Jan-21	£155.54
Feb-21	£189.45
Mar-21	£124.78
Apr-21	£195.21
May-21	£111.54
Jun-21	£119.67
Jul-21	£167.48
Aug-21	£152.89
Sep-21	£133.65
Oct-21	£146.87
Nov-21	£159.58
Dec-21	£136.69

I created a Pivot table, then a Pivot chart with sliders.

I don't want data labels when the data is unfiltered (it's all too messy). But when I filter the slicer, then I would like data labels.

For example, when unfiltered I'd like it to look like this:

Then filtered, like this:

I've done this manually to show what I mean, but I'd like it to be dynamic!

Is this possible?

Thanks in advance

 

[BSALV]

change then number of rows and call it for example in a Worksheet_PivotTableUpdate-event or adapt it there a little bit

Sub Macro1()
     With Sheet("MySheet")
          b = (.PivotTables("MyPivottable").TableRange2.Rows.Count < 40)     'check if number of rows of your pivottable < 40
          With .ChartObjects("MyPivotchart").Chart.FullSeriesCollection(1)     'the corresponding pivotchart
               If b Then
                    .ApplyDataLabels                            'if number is small enough, show labels
               Else
                    .DataLabels.Delete                          'else not
               End If
          End With
     End With
End Sub

 

[reacon84]

Thanks for the reply.

How do I add this to the file? I'm fairly well versed in adding Macros using a button to press, but would this be one that just runs all the time?

 

[BSALV]

i suppose pivottable and chart are in the same worksheet
Put this in the module of that sheet.
Adapt the name of your pivottable and the name of your chart.

Option Compare Text                                             'module is not case sensitive

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
     If Target.Name <> "Desired Pivottable Name" Then Exit Sub  'check if the updated pvt has the right name, else stop

     b = (Target.TableRange2.Rows.Count < 40)                   'check if number of rows of your pivottable < 40
     With Me.ChartObjects("MyPivotchart").Chart.FullSeriesCollection(1)     'the corresponding pivotchart
          If b Then
               .ApplyDataLabels                                 'if number is small enough, show labels
          Else
               .DataLabels.Delete                               'else not
          End If
     End With
End With
End Sub