Recently I have been building some interactive charts for one of my clients using techniques that I have learnt from p3adaptive.com, from searching the Web, as well as some of my own ideas. While some of the techniques I will talk about in this post are not new, I have combined some of these old favourites with some new techniques to solve some of the problems I have come across. I want to illustrate how the combination of these things can deliver a very positive user experience, and just as importantly – anyone with a good set of Excel skills can build an interactive charting tool like this by following the patterns demonstrated. I have created a demo of all of these concepts into a new workbook using Adventure Works so that you can see how these techniques come together for the user. There is a link to this workbook at the end of this post.
The techniques I have used are:
- Disconnected slicers used to create interactive chart series
- Cube formulae and standard Excel to make an interactive chart title
I love these 2 tips I learnt from Rob – so user friendly. However I came across a few problems when I tried to implement these, hence I have developed the following 3 additional techniques to solve these problems.
- Cube formulae and standard Excel to make an interactive legend
- VBA and “link to source” for interactive axis formatting
- Excel VBA to change which Axis the series appears on.
I have created a short video to demonstrate the 5 features built into this workbook, and I then explain each of these in more detail below. I have not hidden the behind the scene workings so you can see these in action – of course you would normally hide these from the user.
Now let me call out the key techniques I have used to make this workbook rock.
Disconnected slicers used to create interactive chart series
This is one of the many techniques Rob covers in his online training course. Essentially what you do is create separate measures for each insight you want to see in your chart. In my example I have created measures for
- Total Sales
- Total Cost
- Total Margin
- Total Margin %
- Total Sales LY
- Chg vs LY
- Chg vs LY %
I then created 2 slicers where the user can select which lines will appear on the chart
When I was building the Switch() function for these slicers to work, I created some quick Excel ‘helping’ formulae to help me write the DAX (in column H).
I then copied the data from column H and pasted it into a switch wrapper I created at https://www.daxformatter.com/ (as shown below).
And then DAX formatter gave me my code nicely formatted to paste as a new measure. Much faster than trying to write this line by line, and this approach ensures you don’t accidentally put the wrong index value against the wrong measure.
Cube Formulae for Interactive Chart Titles
The second feature I have used is to leverage cube formulae to create an interactive chart title. Rob has covered this off in this post, so I wont go into the detail here. Note however that I have concatenated data from 3 different slicers into 1 Chart Title Text, and used the CHAR(10) function to place a line break for each of the title pieces. This makes the title easy to build and easy to read.
And here is another tip regarding titles. When you “insert chart title” for your chart, be careful not to move the title on the chart with your mouse. If you move it, then it will no longer automatically centre the title text on the chart. If you accidently move it, then delete it and add it back again.
Cube formulae and standard Excel to make an interactive legend
Given the chart series will change with the slicer selection, there is a need to let the user know what is being displayed. This is easy if you use a single chart title for this, but it is much harder if you want to change the legend on the chart. Standard Excel allows you to point the series name to any cell in Excel (and hence control the text in the legend), but unfortunately you cannot do this with a Chart that is pointing to a pivot table – very annoying. To get around this problem, I created formulae next to the pivot table that simply replicate the values from the pivot table. I then pointed my chart to this copy of my data – and you can change the series name once you have done this. However given that I then had the data in a new Excel column, I just updated the titles for each of these 2 new columns so that they point to my cube formulae that returns the name of the measure. Now I know this is a hack – I would love to hear if you know of a better way.
VBA and “link to source” for interactive axis formatting
Standard Excel Charts have a feature that allows you to “link to source” so that the chart axis picks up the formatting from the source in the spreadsheet.
First I tried to use conditional formatting of the values in Excel based on the formatting type required for the series (I use cube formulae to indicate which formatting is required), but I then discovered that “Link to Source” doesn’t work with conditional formatting. So I created some VBA code to format the cells next to the pivot table after the pivot table refreshes using VBA and standard cell formatting. That way you can turn on “link to source” in the chart and have the axis in the chart correctly format as needed. The VBA code is triggered from the Worksheet_PivotTableUpdate event to apply the correct formatting. It sets the format based on the cube formulae sitting just above the headings (as you can see in one of the images above).
Here is the VBA Code. Note the bold text below where the code checks to see what formatting is required as returned by the cube formuale.
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error GoTo ExitHere: 'this is needed in case user selects multiple values in slicer
Application.ScreenUpdating = False
If Range("D13").Value = "$" Then
Range("sheet2!D15:D26").NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;[email protected]_-"
Else
Range("sheet2!D15:D26").NumberFormat = "0.0%"
End If
If Range("E13").Value = "$" Then
Range("sheet2!E15:E26").NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;[email protected]_-"
Else
Range("sheet2!E15:E26").NumberFormat = "0.0%"
End If
SetSecondAxisLabel
ActiveSheet.Range("R16").Select
Application.ScreenUpdating = True
ExitHere:
End Sub
Excel VBA to change the Axis for each series
Finally as I started to use Rob’s technique to control which series appears on the chart, I soon discovered a problem when you switch between different series that display Values or Percentages.
I couldn’t find a way to solve this, so I wrote some VBA code to toggle the series so that the user could choose to display the second measure on the secondary axis. (Note: I experimented with allowing the user to control which axis for the second AND the first measure. My advice – don’t go there. Depending on which order you swap the axes using VBA, you can get tied up in a knot where both measures are on the second axis with no way to get them back to the primary axis. Controlling the second measure only proved to be the most useful to me.
I have used Hyperlinks and not command buttons to trigger the VBA code – because Hyperlinks work in SharePoint hosted workbooks but command buttons do not. But alas I have since discovered that VBA workbooks are also not supported in SharePoint either. So it seems that this concept will only work on the desktop. I think hyperlinks are still better, because the user gets visual feedback about which axis the series is set to via the orange colouring.
To implement the hyperlinks, the first thing to do is create the hyperlinks in the 2 cells that toggle the series on the primary and secondary axis. I selected the hyperlink to jump to “Place in this Document” and selected the same cell that contained the actual hyperlink – in other words it hyperlinks to itself.
But more importantly it triggers the “Worksheet_FollowHyperlink” event, and that allowed me to trap the event and execute the following VBA code
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Application.ScreenUpdating = False
ActiveSheet.ChartObjects("Chart 3").Activate
If Target.Range.Address = "$R$16" Then
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.FullSeriesCollection(2).AxisGroup = xlPrimary
Range("T16").Select
ResetFormat
Range("R16").Select
FormatOrange
End If
If Target.Range.Address = "$T$16" Then
ActiveChart.FullSeriesCollection(2).AxisGroup = xlSecondary
Range("R16").Select
ResetFormat
Range("T16").Select
FormatOrange
SetSecondAxisLabel
Range("T16").Select
End If
Application.ScreenUpdating = True
End Sub
This VBA code sits on the worksheet itself and is triggered by the Hyperlink event. There are 2 other Macros that are called from the main macro above (ResetFormat and FormatOrange). These macros simply format the hyperlink cells to give a visualisation of which axis you have selected. Now I am sure that my VBA code is not all as good as it could be, and maybe there are other ways to solve the same problems. I know I should be able to manipulate the ChartObject directly by name rather than first activating it and then manipulating the ActiveChart, however I couldn’t work it out. Please post if you know how to rewrite this code so it is cleaner.
If you have a better or just different way to solve some or all of these problems, please share them in the comments section below.
You can download an Excel 2013 version of this workbook to your PC if you would like to look how I built out these features.
Matt Allington is a professional Self Service BI consultant and trainer based in Sydney Australia.
