In one of my previous posts link – I described how you can create a dynamic pivot chart in Excel 2013 – but it did require VBA to change the fact.
So first I created a power pivot model based on Adventure Works DW, and created some facts
Sales Value LY
Sales Units LY
And the user should now be able to select from a slicer which of these should be plotted in a development chart.
So I start by creating a table in Excel with the facts
Adds the table to the data model in the workbook
And create a measure called
This will be used in another fact which is the one I will use in the pivottable/chart.
So when I create a pivottable the Selected fact will return the ID of the fact when the Fact names are placed on rows or columns.
Then I create a dynamic fact calculation
Fact to plot:=SWITCH([Selected fact];1;[Sales Amount];2;[Sales Amout LY];3;[Sales Units];4;[Sales Units LY];5;[Average Price])
So adding the fact to the pivottable it looks like this – with the correct value under each fact – DAX magic 🙂
You will get a warning about missing relationship but you can ignore this.
So Insert a slicer with the Fact as the slicer
And now I can filter the table with the slicer
And now you can create a pivot chart based on the pivot table and add a slicer for the year and other dimensions if needed..
OBS – you cannot use this technique with the new pivotchart in Excel 2013 but you have to have a pivottable behind the chart – but you can put that on a hidden sheet if needed.
You can download the example file from here – Download