skip to Main Content

 
Excel Pros Searching for a Worthy Challenge

“Shooting space garbage is no test of an Excel Warrior’s mettle!

I think it is appropriate to start this post with the following disclaimer: 

You do NOT have to be able to win this contest in order to be a monster at PowerPivot.

Think of this as brain candy.  Something that opens your eyes to some of the high-end power available when you merge PowerPivot calcs with native Excel calcs.  A 300-level technique.

***CONTEST UPDATE***  Check here for 3 small rules clarifications.

The Goal:  One Slicer Sheet Impacting Many Report Sheets

This is a pretty common desire.  If the report consumer wants to see ALL of the data broken out by the same set of filters, why make them repeat those filter selections on every single sheet of a workbook?

Isn’t it better, in those cases, to give them a single set of “master” slicers on a single sheet like this?

Slicer Home Page 
Master Slicers Sheet
(Sensitive Data Redacted as Always)

There are 14 slicers on that sheet.  The user can select the department, class, etc. that they care about up front.

Once they have made their selections, in this particular workbook they then have dashboard sheets that look like this:

Dashboard Sheet
Multi-Chart Dashboard Sheet Fed from PowerPivot 

We have several sheets like that in this one workbook, with 20-30 charts on each.  All charts react to the slicers on the Master Slicers sheet.

But you’ll also notice that 4 slicers appear on this sheet, too.  Those are duplicates of 4 of the slicers on the master sheet.  They are the same exact fields as the corresponding 4 on the master sheet, so they are always in sync – set them on one sheet and all other sheets reflect the same selections.

That is merely a convenience – we figured that most slicer selections would be made once, but that users would appreciate being able to bounce around the calendar a bit with the time slicers, without having to switch sheets.

There are also about 20 sheets in the workbook that look like this:

Full Chart Sheet
1 of 20 Single-Chart Sheets Fed from PowerPivot

Again, with the four time slicers repeated for convenience.

The Snag:  Slicer Cross-Filtering Performance

80 unique charts and 14 unique slicers.  What could go wrong?

Well, as I documented in the post on slicer cross-filtering, this is roughly the equivalent of having 80 * 14 = 1,120 pivots all updating at once in response to a single click.  (Maybe it’s not quite that bad, but it DOES get VERY complicated).

And that is very slow, even if the performance of any single pivot is super-fast.

But our report that we built at Pivotstream is fast.  It does NOT bog down on slicer cross filtering performance.  So, how did we do it?

The Contest:  Find Efficient Techniques!

After we built this workbook at Pivotstream I realized that our technique could be refined quite a bit, made simpler.  And while we are already doing that, I realized:

THIS IS AN EXCELLENT CONTEST FOR EXCEL PROS!

I still have two MSDN Subscriptions to hand out.  These basically are unlimited, free MS software for your own personal use, so they are a pretty damn good prize.

The top two submissions will win those.  Runners-up will win PowerPivot architecture posters from Denny Lee, assuming he has not run out 🙂

Rules Schmules!
 

  1. Use lots of charts, and have them all respond to a shared set of slicers
  2. Entries must include your PowerPivot workbook, plus an explanation of what you did
  3. Use any data source you want, but please use non-sensitive data and enough rows that we can spot performance differences
  4. Use at least 6 slicers, with cross-filtering enabled
  5. You do NOT have to use PivotCharts, but you can
  6. Macros can be used to design the report if you find that helpful, but should NOT be part of the update process when I click a slicer.  (Authoring time is ok.  Run time is not.)
  7. Entries will be judged on, in roughly descending order:
    1. Performance – I will compare query speed with slicer cross-filtering turned on vs. turned off.  Smaller differences are better for this contest.
    2. Polish – as I slice the report, do the charts all still look nice?  Ex:  Blank space in charts = not good.
    3. Ease – how hard is it to execute your technique?  We will turn winning entries into blog posts, and this will become a new reference technique here at PowerPivotPro.
    4. Originality – this never hurts but is not crucial.  Fun is good.  Unexpected benefits and features are even better.

Have fun 🙂

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 3 Comments
  1. Boy this was a LONG time ago. If memory serves, no one even submitted an attempt at an answer.

    I’m pretty sure our answer, back then, was to create a single hidden (and flat) pivot table that had ALL of the data required to power all of the charts.

    And then all of our charts were just regular charts, not pivot charts. And they all referenced into that hidden pivot as if the pivot were just normal sheet cells.

Leave a Comment or Question