PowerPivot Data in Scatter Charts, etc.

This was going around on the MS-internal discussion list this past weekend:

At right is a list of all chart types in Excel.  But not all of them are supported as PivotCharts.  Try to use XY (Scatter), Bubble, or Stock as a PivotChart, and you’ll get the alert below:

So, do you give up?

Nope.  Remember the post where I converted a PivotTable to Cube Formulas using that button on the ribbon?  This is a wonderful opportunity to use that feature in an unexpected way.

Once it’s converted to formulas, you can create any chart type you want against it, no restrictions.  It’s just regular old cells now.

Neat huh?

But what about grow/shrink?

The next question is usually “OK great I can scatter chart my data points.  But tomorrow, I may have a different number of data points.  Will my spreadsheet pick that up?”

The answer is “no, not even close.”  🙂  When you convert to formulas, the original list of rows/columns is fixed, by virtue of the CUBEMEMBER function that makes up the axes.

Intrepid PowerPivot adventurers, however, may want to try out the following technique, by which the data WILL be picked up in your chart.

Oh no, not CUBESET again!

Oh yes.  CUBESET.  Again.  You’re gonna use that CUBESET function, and you’re gonna like it!

But you might have a love/hate reaction to some of the other Excel acrobatics I am about to share 🙂  For something that Excel actually supports natively, and seems to have gone out of its way to support, this should be easier to do than it is.  But why complain?  Fact is, it CAN be done, and it feels like magic.

It will be easiest if you follow along on your own desktop, so PLEASE GRAB THE WORKBOOK FROM HERE – that contains the results of everything I show below.

OK, you have the workbook now, right?  Let us continue.

To simulate the effect of getting more/less data, modify the Set in cell A1 to grab any column out of [DimCustomer] that you’d like.  Since each column has a different distinct number of members, you’ll get behavior that is essentially the same as more/less data after a refresh.

The “magic” here lies in two things.

First is defining named ranges that refer to an OFFSET formula rather than a fixed range:

Here is that formula in case you want to copy paste it:

     =OFFSET(Sheet1!$C$4,0,0, COUNT(Sheet1!$C$4:$C$5000),1)

The formula I used for YRange is simply another offset off of that first named range (XRange), using this formula:

     =OFFSET(XRange,0,1)

Second is this:  YOU MUST NOT RELY ON THE REFEDIT CONTROL TO POINT YOUR CHART AT YOUR DATA

The reason is, that refedit control will eat your dynamic named range and convert it into a fixed range, destroying all of that incredibly obscure work you did above J

This is the chart refedit control, for reference.  What you put in here will NOT adjust, ever.

Instead, do this:

1. Create the chart.  Point it at whatever data you currently have, as if it was going to be a static chart (yes, use the refedit control for now)
2. Now, in the created chart, select the data range within the chart.  If you do this right, your data points will get selection handles and the SERIES formula will appear in the formula bar, like this:

3. Then you edit that formula, replacing any range of cells with your dynamic named ranges, XRange and YRange

   a. Note that I’ve already done that in the picture above

   b. Also note that you must prepend the name of the workbook, then a “!”, to your names in order
        to make them work

   c. You must also perform the happy Excel dance in order to appease the ancient lords of recalc

Also of note:

1. I only copied the formulas down to row 5000, and set my named ranges to cap out at row 5000 as well.
2. Some fields will overshoot that 5000th row.  I could have gone farther of course, I just got bored.  You can pick a bigger number.
3. I wrapped all formulas in IFERROR so that I don’t get error popups.
4. If you don’t mind popups, I believe this whole thing works without IFERROR
5. If one of your cube formulas returns an error for any reason other than running off the end of the data, you will miss data points
6. That’s because the dynamic named range is based off of COUNT, rather than off of finding the last valid data point
7. Even if I had not used IFERROR, the errors trigger the same problem
8. It might be possible to fix this problem using INDIRECT and some even more arcane techniques

Simple, right? 🙂