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.
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 & “]”)
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.