skip to Main Content

image

 

Neo: Are you saying that I can copy/paste formulas?
Morpheus: No Neo, I’m saying that you won’t have to.

 

 

Hi folks.  Sorry about the lack of posts lately.  I’ve been on the road.  A lot.  This week I’m doing a doubleheader – consulting/training in New Mexico and then California, so I’m writing this on a plane between Denver and San Diego.  (I recently joked that I’m the PowerPivot version of Tyler Durden – never sleeping, crisscrossing the country setting up franchises of Pivot Club).  PowerPivot adoption is rapidly picking up and that has the ironic side effect of taking me away from the blog.

But the other reason I’ve been so quiet is that I submitted a two-part post to Microsoft’s official Excel blog rather than posting it here.  Some posts just “speak” to the Excel audience better than others, and this is one that I really want as many Excel folks to see as possible.  The majority of Excel pros have yet to discover PowerPivot, and I love being the bearer of good news.

The post is actually about an inherent weakness in all traditional spreadsheets.  One that I believe eats more than half of the average Excel pro’s time, if not more.  A weakness that PowerPivot obliterates… with a concept that I am calling Portable Formulas.

Part one of two is live today: Click here to view it on the Excel blog.

Rob Collie

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 5 Comments
  1. Folks, I am hoping to have part two published ASAP, but I’m not sure the Excel blog’s packed calendar can accomodate that. I’m working on them to move it up 🙂

    If you want to help the cause, go post these comments on the Excel blog 🙂

  2. Hello Rob, enjoyed the second part very much. I can not seem to find a way to post a comment over on the Office Blog so therefore I am commenting here.

    I used:

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

    and it works on the ‘Month’ level but the GRAND TOTAL number is ‘screwed up.

    Example: If Jan to Dec are let’s say around $100 for 2010 and 2011… the Grand Total should be around $1,200 but instead it is $100.

    What is going wrong?
    Thx

    1. Hi Carsten. The measure [$ per Month] is inherently the division of grand total by number of months.

      When you go back a year from the grand total cell, you get the full $1200 numerator but you also get 12 months. So it divides out similar to a one-month total – it’s the dollars per month experienced over the entire year.

      Good news: this exact issue is addressed here: https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/

      🙂

Leave a Reply

Your email address will not be published. Required fields are marked *