All Chart TypesThis 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:

Illegal PivotChart Types

 

So, do you give up?

Convert to Cube FormulasNope.  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:

clip_image002

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.

clip_image004

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:

clip_image006

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? 🙂

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 22 Comments

  1. […] the original:  PowerPivot Data in Scatter Charts, etc. « PowerPivotPro By pivot | category: pivot data | tags: column-out, different-distinct, each-column, […]

  2. Rob, why not use the method that Excel users have had to adopt for ages to get around the PivotTable chart limitations and its (pre-2007) sadistic behavior of resetting chart formatting at the slightest provocation i.e. create a regular chart from a PivotChart? The advantage of the standard chart solution is that it works with all PivotTables (cubed based or not). You can still use dynamic chart ranges as you discuss. Of course, if you’re converting to cube formulas for other purposes anyway, it’s a moot issue.

    1. Good question Colin. I have an excellent answer:

      “Because I’ve never done that before.”

      See my reply to Geb below. I think I have a disporportionate share of the microphone here and I need to open it up a little bit.

      Fact is, you can work ON Excel for years, but when you go out and visit someone who works WITH Excel everyday, they often show you a technique (or even a feature!) that you weren’t aware of before.

      In fairness, it was usually a 2-way street. I had things to show them, too. Mr. Excel and I have swapped a few great ones over lunch.

      But working on the Excel product simply doesn’t translate to omniscience in the way that working on, say, Word might.

      I need this site, then, to be a little bit more of a conversation and a little bit less of a broadcast. Stay tuned.

    2. Colin, would you be willing to direct me to a post on how to work around scatter plot in a pivot/powerpivot?

      Thanks,

  3. hint hint for more arcane formulas,

    use indirect() with a cubesetcount(cubeset) as your offset and you won’t care about the 500 rows.

    1. “Check out the big brain on Geb!”

      -Jules Winfield

      That’s excellent. I still need to fill the CUBERANKEDMEMBER formula down past row 5000, but that would account for all of the other problems.

      You guys are inspiring me. I have ideas relating to how experts such as yourselves can contribute (and get credit for your ideas). I’m no slouch at this stuff, but you guys are fantastic, and the back-and-forth collab between everyone is even better.

      So yeah, I’d like to take you up on your prev idea of submitting workbooks with examples. Look for a new post soon on how we can do that 🙂

  4. Unless I’m missing something, I’d use COUNTA instead of COUNT, and set up the dynamic range as =OFFSET(Sheet1!$C$4,0,0,COUNTA(Sheet1!$C:$C)+2,1) or Sheet1!$C$1:INDEX(Sheet1!$C:$C,COUNTA(Sheet1!$C:$C)+2). COUNTA works with or without errors in a cell.

  5. “Because I’ve never done that before.”

    Rob…I humbly apologize for the presumption (I feel pretty silly). Given the issues surrounding pre-2007 PivotCharts, and the plethora of MVP discussions on the topic over the years, I falsely assumed that the workaround using regular charts was generally known within the Excel team. In the Excel 2007 blog discussions, for example, the issues and workaround were acknowledged by Dave Gainer
    http://blogs.msdn.com/excel/archive/2006/04/19/579649.aspx.

    Mind you, Excel tries very hard to prevent you from creating a regular chart from PivotTable data.

    Apart from the unsupported charts problem, there are certain combo charts that are impossible to build using a PivotChart created from a standard PivotTable. In many of these cases, the sheer flexibility of PowerPivot’s calculated measures and filters make these charts possible.

    1. There is no need to apologize Colin 🙂 We all end up knowing tricks that others don’t. That’s what makes community interaction around thing like Excel and PowerPivot so much fun for me 🙂

  6. Hi, thanks for sharing this! I am trying to label the data points.. i.e.. Project 1, 2,3.. based on the above method, I can only plot it as series 1,2, 3…. thank you inadvance

  7. Hi, thanks for sharing this information which is very useful. But I am facing other problem. I am ploting graph using power pivot table data in which i want one series to be BAR chart and other as LINE chart. It is possible to do this but i am giving some slicer connection to this graph and when i select those values in slicer for which no data is available then a blank graph is coming that is correct But when i am unselecting those slicer then both the chart type is turning into BAR graph only (Line graph coverts into BAR graph)

  8. Hi,

    Unfortunaly not simple,

    I followed your instructions (which are genial), but in my case it is not presents every data points.

    I have a database with locations, trial numbers variety names and performance data.
    I used the slicers than I change for formulas it work well at first glance. My problem is if I change something in the slicers (e.g. other trials or locations) I can see only those first points – from the charts related data selection in the sheet (behind there are the formulas)- which equals the number of elements in the other trials not the whole column, so if I have more data I see less.

    How could I set it to have a dinamism with the changes?

    If is is not understandable I would send you a copy tho explain.

    Thanks in advance.

    Lollek

  9. I can’t figure out what I am doing wrong. I’m using Excel 2013, and if I create a powerpivot table using powerquery data loaded into a powerpivot model, the OLAP option is available to me. When I just use data in a table to create a non-power pivot chart, the OLAP option is greyed out. Makes sense to me, but I am not finding a way to convert my chart to a bubble chart when I use the powerpivot table and chart. What am I doing wrong? Any guidance would be greatly appreciated.

  10. I followed all the steps but the final step didn’t work, when I tried to edit the formula of the series. Nothing changed?

Leave a Comment or Question