Waterfall PivotChart?

[Ken Puls]

I know building a waterfall chart is possible, but my first crack at this using a PivotChart isn't bringing me a lot of joy here.

I'm trying to using some PowerPivot data to visually show:

• Gross Pay
• Benefits
• Deductions
• Net Pay

I'm thinking a waterfall would be pretty good for this, as I can show each step up/down. The issue I have here is that I'm linking this to PowerPivot measures and filters to get my total gross pay, benefits, etc...

Before I waste a ton of time, I'm kind of curious if this is a non-starter. Do you need more granular control over the chart/data than a PivotChart gives you? I can probably revert to using OLAP formulas to source me data and use a real chart if I need to.

Thoughts?

 

[eferrero]

Hi Ken,

I'm guessing you use Excel 2010? Select the PivotTable, Options Ribbon, Options drop-down, uncheck the Generate GetPivotData. Then use dynamic ranges to select the data you need and build a waterfall chart as normal.

 

[Ken Puls]

Hi Ed,

Yes, I'm using Excel 2010.

So I couldn't figure out how to make the PivotChart work for a Waterfall chart. I got stuck trying to figure out how to manipulate the data to work. Instead I elected for the following:

• I added a slicer to my PivotTable
• I used a formula to pull the employee name out of the slicer (thanks to Dick Moffatt's post on Rob Collie's blog)
• I used four OLAP functions to pull my required data out of a PowerPivot cube
• I built a regular Waterfall chart per Jon's instructions, using the OLAP formulas to populate the key areas

It seems to work quite nicely, although is a bit slow to refresh.

After that I thought it might be nice to add some extra charts to expand on the data a bit. So I added a few simple pivot tables, linked them all to the slicer, and built PivotCharts off of them. They refresh quite quick.

While I'm not sure I'm totally sold on the format of the supplemental charts, I think I'm headed down the road I wanted to. The Waterfall chart is certainly where I wanted to go.

I've attached a copy of the file (using sanitized data) if you're interested.

 

[Ken Puls]

I forgot to mention this...

One issue I had with the previous post is that my waterfall chart always referred to the first selected employee in the slicer, even if I grabbed a group. Because the other three charts were pivot charts, they updated, but the waterfall didn't.

I fixed this by re-enabling the "generate GETPIVOTDATA" setting, then pulling my totals for the waterfall chart from the pivot table totals. Works faster now and can deal with multiple employees.

Revised version attached if anyone is interested.