Back
Excel

Portable formulas: A PowerPivot benefit you won’t believe you lived without, part 2

Former Excel team member Rob Collie is CTO of Pivotstream and writes the PowerPivotPro blog, describing his experiences adopting Excel PowerPivot.

Hello again, I’m back for part 2. If you missed part 1, I highly recommend reading it first, because this won’t make much sense on its own.

Same examples, this time WITH PowerPivot

OK, now we’re going to replay part 1, but this time we are going to use PowerPivot.

Let’s start with a basic PivotTable that looks like one of our first steps in part 1. You check four check boxes, and here’s what you’ve got:

just 4 clicks and the Month/Year/Category problem from before is covered just 4 clicks and the Month/Year/Category problem from before is covered

No formulas needed so far… just 4 clicks and the Month/Year/Category problem from before is covered

I did NOT need to write a formula in order to get “Sum of Sales” in the pivot – I just checked the Sales check box, and it automatically handles some of the problems from above.

But I CAN write a formula for Sales if I want. It’s a pretty simple formula:

=SUM(Sales[SalesAmount])

For space considerations, I’m not going into the details of that formula, but it should be pretty easy to get the idea. It yields the same results as above, but it ALSO adds a new check box to the field list:

If you write a formula for sales, it becomes its own check box

If you write a formula for sales, it becomes its own check box

For extra clarity, I like to express my measure formulas as “[MeasureName] = <formula>.” In this case, that is:

[Sales] = SUM(Sales[SalesAmount])

Our first example of portability [Sales] can be used in other pivots!

That behavior – the formula getting saved as its own new field (checkbox) provides a lot of benefit. The simplest demonstration of said benefit is this: You can now use your [Sales] formula in other pivots, even on different worksheets!

For example, here’s another pivot showing [Sales] broken out by DayOfWeek, and [Sales] did NOT need to be rewritten, all I needed to do was click the check box:

Reusing a PowerPivot formula in other pivots: You just click the check box

Reusing a PowerPivot formula in other pivots: You just click the check box

For a formula as simple as [Sales], this may not be a huge deal. But as your formulas get more complex, it is a MONSTER time-saver.

Portability example #2: Using 1 formula inside another

Formulas being saved as new fields will come in handy in our next step, too, which is calculating “Dollars per Month.” I can use the [Sales] formula I defined above as an input to my new formula!

[$ per Month] = [Sales] / COUNTROWS(DISTINCT(Sales[MonthNum]))

Which, given the definition of [Sales] above, is *precisely* the same thing as rewriting the [Sales] part as a SUM:

[$ per Month] = SUM(Sales[SalesAmount]) / COUNTROWS(DISTINCT(Sales[MonthNum]))

But using [Sales] instead of rewriting the SUM saves me time. That savings becomes more pronounced when your original formula is more complex, of course. And as an extra benefit, if you need to change the way [Sales] is calculated later, you can do that in one place, and all of your other formulas that depend on it will also pick up the change!

(As incredibly useful as they are, I don’t have room here to describe the new functions COUNTROWS and DISTINCT, but it should be easy to get a feel – we are taking the distinct count of months and using then dividing our sales amount by it. If you want to know more, I highly recommend you check out this example, it includes short video tutorials.)

The formula just works. It does NOT need to be modified to when I add Month to the pivot – it automatically adjusts itself! More on this later.

$ per month formula – it just works

$ per month formula – it just works

Let’s round out the example with a formula for “$ per Month Prior Year”.

[Prior Yr $ per Month] = [$ per Month](DATEADD(Calendar[Date],-1,Year))

And then use that to define $ per Month Pct Growth vs. Last Year:

[$ per Month Pct Growth vs. Last Year] = ([$ per Month]-[Prior Yr $ per Month])/[Prior Yr $ per Month]

Yielding our final result:

Final result with new formula

Same result as traditional formulas but in a fraction of the time

If, later on, I needed to change the formula for [Sales] to be something different, like to subtract out [Credits] or something like that, that fix will automatically flow through to [$ per Month], [Prior Yr $ per Month], and [$ per Month Pct Growth vs. Last Year]. All of my reports will just update automatically, no surgery required.

Portability example #3: Formulas adapt to new report shapes automatically

Benefits 1 and 2 would be enough to make this a valuable addition to Excel. But benefit 3 is probably the most magical of them all.

I can change the layout of the report above by removing Category, and then I even filter out some years, my formulas still work:

Dramatic rearrangement and filtering of the pivot, and formulas still just work

Dramatic rearrangement and filtering of the pivot, and formulas still just work

Even more dramatic: Even though my “$ per Month” formula includes a calculation of the number of months, the formula works even when Months are NOT displayed on the pivot:

Even though the formulas calculate number of months, they still work without months displayed!

Even though the formulas calculate number of months, they still work without months displayed!

Portability example #4: Reuse measures in other workbooks

Last, and this is purely a bonus at this point: If you adopt PowerPivot for SharePoint,  you can reuse these measures across workbooks:

Define measures in a “core” workbook, use them in pivots in “thin” workbooks

Define measures in a “core” workbook, use them in pivots in “thin” workbooks

Seriously. The measures I defined above like [$ per Month] can be reused in pivots in completely different workbooks. And again, I don’t need to rewrite them to do that, I just “connect” my new workbook (I call that workbook a ‘thin”) to the original workbook (which I call a “core”), and then just check a check box in the field list. Also, if I later change the formula in my original workbook, new results will show up next time I refresh the pivot in the other workbook.

I could write an entire post on this, so if you want to know more, please check out this blog post.

Conclusion

As I said in part 1, I believe that a very large percentage of the time spent in traditional Excel is consumed by compensating for the lack of formula portability. Everything just flows in PowerPivot though, so you can focus your time on designing smarter formulas, and then using those formulas basically everywhere.

Not only does it save tremendous amounts of time, but it opens up new lines of thinking that you never would have thought to pursue before. The rewriting and reshaping of formulas would just have been too painful without PowerPivot. What used to be your entire afternoon is now as simple as a few clicks.

The resulting freedom has literally become my entire career. At Pivotstream, our small team of analysts performs analysis and reporting work for dozens of clients, and the only tool we use is Excel with PowerPivot. That sort of scale simply was not possible before. If you’d like to get started, I’ve collected some links below.

Further suggested reading on PowerPivot

Getting started With PowerPivot

  1. Download PowerPivot – Get PowerPivot for yourself (the Excel component is free, and there’s even a free trial of Excel 2010), plus tips on installing it.
  2. Top 5 Ways PowerPivot Helps Excel Pros – Another post here on the Excel blog.
  3. What is PowerPivot – Introduction to what it’s all about.
  4. Temperature/Sales Mashup Demo – Combining sales data with temperature statistics off of the web to do something truly unexpected. (And part 2 is here.)
  5. Your First PowerPivot Workbook – Want a jump start?  Grab the workbook from the Temperature Mashup demo for your own reference.

The FAQ

The Great PowerPivot FAQ is a collaboration by myself, members of Microsoft, as well as other leading experts in the Excel community.

Case studies and in-depth examples

  1. 48-Hour Case Study from Bing – How a team at Bing adopted PowerPivot for internal bug tracking in literally 48 hours, with no prior experience.
  2. The Great Football Project – Converting a $50,000 business intelligence project to Excel and PowerPivot.
  3. Extranet Analytics Using PowerPivot – Using PowerPivot as a web application platform.

Observations from hands-on PowerPivot training

  1. Observations from PowerPivot Training and Consulting – Reactions from teaching other organizations how to optimally utilize PowerPivot, and their reactions.
  2. James Bond, International Man of Spreadsheets – Why PowerPivot, in the hands of Excel pros worldwide, is about to change the world. Literally.

-Rob Collie

A former member of the Excel team for versions 2003 and 2007, one of the founding engineers of the PowerPivot team at Microsoft, and now CTO at Pivotstream, a cutting-edge adopter of Excel 2010 and PowerPivot. Rob has also been writing the PowerPivotPro blog for the past year and a half, covering his experiences adopting PowerPivot, applying it professionally on an industrial scale, and traveling the country training others on how to adopt it. You can also follow him on Twitter as @powerpivotpro.