How to create a dynamic PowerPivot Chart in Excel 2013

One of cool new features in Excel 2013 is that pivotchart’s no longer are tied to a separate pivot table, but are decoupled.

This also means that is easier to manipulate via code.

 

So I have added 4 shapes above the chart so the user can click the shape in order to change the value in the chart.

In order not to create a separate macro for each shape, so I named each shape to the Measure name in the PowerPivot model.

And then created the following macro.

Sub SwitchFact()

Dim ws As Worksheet

Dim cht As Chart
Dim strFact As String

‘Application call will return the name of the shape

‘So name the shapes

strFact = Application.Caller

‘Set a reference to the sheet with the table

Set ws = ThisWorkbook.Worksheets(“ScoreCard”)

‘Set a reference to the chart

Set cht = ws.ChartObjects(“Development”).Chart

‘Remove the previous fact

cht.PivotLayout.PivotTable.CubeFields(cht.PivotLayout.PivotTable.DataFields(1).Name).Orientation = xlHidden

‘Add the selected fact

cht.PivotLayout.PivotTable.AddDataField cht.PivotLayout. _

PivotTable.CubeFields(“[Measures].[” & strFact & “]”)

End Sub

So I use the Object Application.Caller to return the name of the clicked shape and can then pass it on to add the selected datafield.

Assign this macro to all the shapes and you are ready to change the measure in the pivotchart.