skip to Main Content

by Matt Allington

Recently I have been building some interactive charts for one of my clients using techniques that I have learnt from powerpivotpro.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:

  1. Disconnected slicers used to create interactive chart series
  2. 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.

  1. Cube formulae and standard Excel to make an interactive legend
  2. VBA and “link to source” for interactive axis formatting
  3. 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

  1. Total Sales
  2. Total Cost
  3. Total Margin
  4. Total Margin %
  5. Total Sales LY
  6. Chg vs LY
  7. Chg vs LY %

I then created 2 slicers where the user can select which lines will appear on the chart

choose series

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).

excel formual to build switch

I then copied the data from column H and pasted it into a switch wrapper I created at http://www.daxformatter.com/ (as shown below).

paste into daxformatter

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.

results from daxformatter

 

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.

char10

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.

change legend name

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.

linked to source

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.

second axis

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.

insert hyperlink

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.

Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia. Visit Matt's blog here.

This Post Has 20 Comments
  1. This is one heck of a nice book. I really like the ability to select a second axis. The UI of this book is very nicely laid out as well. My hats off to you Matt, I know I will find my self using examples of this book in future projects.

  2. Hi Matt,

    I have been optimising VBA code for 15 years to maximise speed and minimise visual disruption to the user.

    To avoid selecting the chart in the VBA for the Hyperlink event try this:

    If Target.Range.Address = “$R$16” Then
    ActiveSheet.ChartObjects(“Chart 3”).FullSeriesCollection(2).AxisGroup = xlPrimary
    Range(“T16”).Select
    ResetFormat
    Range(“R16”).Select
    FormatOrange
    End If
    If Target.Range.Address = “$T$16” Then
    ActiveSheet.ChartObjects(“Chart 3”).FullSeriesCollection(2).AxisGroup = xlSecondary
    Range(“R16”).Select
    ResetFormat
    Range(“T16”).Select
    FormatOrange
    SetSecondAxisLabel
    Range(“T16”).Select
    End If

    To avoid selecting the ranges add a RANGE input parameter to the ResetFormat and FormatOrange subroutines and adjust the code in the subs to use the RANGE instead of the selected cell.
    Then, without selecting them, call them as below:

    ResetFormat Range(“R16”)
    FormatOrange Range(“T16”)

    I avoid selecting ranges and objects so that the user isn’t surprised or confused by the selected cell changing without him making the selection himself.

    Dave

  3. Thanks Dave, I am pretty sure I tried the first suggestion and couldn’t get it to work. Intuitively that should work, but it didn’t for me. When I code in these ‘less frequent’ areas, I normally use the VBA recorder, and then tidy up the code.

    So the recorder will give me this

    ActiveSheet.ChartObjects(“Chart 3”).Activate
    ActiveChart.FullSeriesCollection(2).AxisGroup = xlPrimary

    and I will trim it to this (which is what you suggested).

    ActiveSheet.ChartObjects(“Chart 3”).FullSeriesCollection(2).AxisGroup = xlPrimary

    Trouble is I just couldn’t get that to work – still can’t. I would love to know why not

    Regarding the second suggestion ResetFormat Range(“R16″), yes I should have done that – good suggestion 🙂

  4. Hi Matt,
    Beautiful concept and report: Interactivity made easy and useful.

    Re your question on how to deal with pivots when they start to get annoying once it comes to charting: Simply blow them up! (Just to use Rob’s phrase here for “convert your pivot table into cube formulae” :-))

    If you then dynamize the cubevalues by changing the column headers: CUBEMEMBER(“ThisWorkbookDataModel”;”[Measures].[Series 1]”) into CUBEMEMBER(“ThisWorkbookDataModel”;”[Measures]. [“&Sheet1!X2&”]”), they will then fetch the slicer-selections dynamically ([Series 2] respectively onto X3).
    Nice side-effect: You don’t need the additional measures which you created using the switch-function (Series 1 and Series 2).

    You can now apply your formatting macro and refer your chart to these original columns.
    OK – by blowing up your pivot table you’ve also lost your trigger for the formatting macro: “Worksheet_PivotTableUpdate (…)” – just change it to “Worksheet_Activate()” and there you are.

    1. Thanks for the suggestion. Of course! You are exactly right and I should have thought of that. Sharing ideas almost always makes things better. I guess the macro trigger would need to be worksheet_change() as there is no activate event.

  5. Hi thank you for this wonderful post! I am following almost all of the techniques as described by you for one of my current projects. However, I am stuck while using the dynamic legend technique. I have added formulae beside my pivot table just as you have described. But I am wondering how have you managed to get the month names on the X- axis if you are pointing your chart to the two formula generated columns? I’m sorry if my question is silly but will be very grateful to get an answer as this is an urgent project I’m working on. Many thanks in advance!

    1. HI, glad you liked it. To get the X Axis to point to the right values, you need to do the following (I am using Excel 2013):
      1. click once on the chart to select it.
      2. on the Ribbon, click Design\Select Data
      3. in the right hand box (Horizontal, Category Axis Labels), click the Edit button
      4. with the data input box selected, you then point to the location in your spreadsheet that has the X Axis data labels.

      1. Thanks so much Matt! I was always much into VBA.. its such a surprise there is so much that can be done in excel reports without VBA. Your blog has been very helpful to me to get into the world of powerpivots! 🙂

        One more qtn – Is there a way to dynamize the spacing of the chart series based on slicer selections. For example, for some selections there may be data only for Jan to April. Can we make the chart resize to show only Jan to April on the axis and remove the blank space that remains for the rest of the months? This works for Pivot charts but am wondering if we can get the same for standard excel chart?

        1. Pivot tables do this automatically, but normal ranges don’t respond to the change in the number of values at all – which I guess you know 🙂

          Personally I don’t like the way that Pivot Charts change size based on the data selected. You can see this behaviour (that I don’t like) by looking at my online hosted demo at this link (note the user ID and password are embedded in the link. Some browsers will warn of a possible Phishing attack – this is definitely NOT a phishing attack, just a convenient way to log on to my hosting demo)

          https://ppdemo%40xcbs.com.au:[email protected]/SitePages/Home.aspx

          Go to the above link and click on year 2005. You will see the chart change size because there is not a full year of data – like you suggest.

          One thing you could try is to alter your formula (that the chart points to) so that it returns an =NA() when there is no data coming from the pivot table. Charts respond well to the #NA error which is generated by using =NA(). When the chart sees “#NA” it simply ignores it. Give that a try and see if it helps.

  6. Thanks a lot for that post, it has been very useful !

    An additional question : I was trying to define the X category axis with slicers as well. Is this possible?

    So far i got close by adding another index table for categories in powerpivot, then creating a custom column in my fact table, with a selector measure that gives MIN(NewTable[ID]). The formula is :
    =SWITCH( [selector_3],
    1, [category_1],
    2, [category_2],
    3, [category_3]
    )

    The column is populated by [category_1] values by default, which is fine. Unfortunately, the disconnected slicer doesn’t seem to trigger a recalculation of the custom column in powerPivot, so I can’t get the other categories.

    Is there a way around that? Or another way to activate X categories with a slicer ?

    Thanks!

    1. Slicers definitely do not affect calculated columns – so that wont work. There has been a discussion at http://powerpivotforum.com.au on this same topic. I don’t have an answer but it would be great if you pooled your thinking with the others thinking about this.

      http://powerpivotforum.com.au/viewtopic.php?f=6&t=99

      My view (for what it is worth) is it is better to clone the worksheet, modify the chart on the “copy” and then put hyperlinks between the worksheets. That way when people want to change the chart, they can just click on the hyperlink and then they are moved seamlessly to a different chart – probably without them even knowing (especially if you hide the tabs).

  7. I may be missing something, but I use slicers in calcs all the time, as well as in charts that are connected to a database so the user has the ability to change the chart. It may not change the column in the Powerpivot window, but it surely can be used in a linked back table with a dynamic date.

Leave a Comment or Question