Results 1 to 4 of 4

Thread: Waterfall PivotChart?

  1. #1
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14

    Waterfall PivotChart?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  2. #2
    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.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •