Skip to content
February 6, 2013 / Erik Svensen

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.

test

 

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: