skip to Main Content

power pivot to power bi

A Power BI Technique Mined from the Power Pivot Archives

Below, you will find one of our all-time favorite Power BI techniques. In the ten (yes ten) years we’ve been operating this site, we’ve written over 1,000 articles on Power BI.  Hard to imagine - even for us - but true.

Years ago, we first wrote up this technique in the context of Power Pivot – Power BI’s cousin and predecessor, which we like to call “Power BI in Excel.” 

Since the two products share the same brains (DAX and M), this technique is more relevant today than ever. Everything below is 100% legit for Power BI – the same data model “shape,” the same DAX, etc. – but now you can use it to power up your Power BI visuals, and not just PivotTables.  Enjoy! Smile


Why am I doing this in PowerPivot?  Primarily as a challenge.

This is a question I should have answered before I even started down this road.

To be honest, I did it primarily as a challenge – to stretch my brain a little bit.  If I were faced with this exact same task in my daily work, undoubtedly I would just use normal Excel formulas.  In some ways, this modeling exercise has been a deliberate misuse of PowerPivot.  A handful of parameters with no source data whatsoever – this is NOT what the PowerPivot engine was built for, which explains why the PowerPivot solution is actually significantly more difficult than the Excel solution.

“So you’ve been deliberately wasting our time??”

No, I do think there is real value in this exercise, for two reasons:

  1. Brain-stretching with new techniques always comes in handy later.  For instance, on the first post Sergey commented that he’d been thinking about loan amortization measures and this could be applied to that.
  2. I can see this technique being added, as a supplement, to a broader PowerPivot model.  For instance, a model containing lots of real customer data over time, and then a [Projected Customers] measure that forecasts future customer populations based on various assumptions and/or marketing investments.

So with that in mind, here it is:  the final installment of viral/marketing modeling in PowerPivot.

Supplementing Viral Growth with Real Advertising

The last part of Rahul’s model that I will address is this:  what if we also spend money on advertising?  How much do we have to spend to make a difference?

There are two additional variables to add: 

  1. How many advertising “impressions” per month do I buy – the number of times potential customers see my ads.
  2. What’s my conversion rate – what percentage of the impressions lead to people purchasing or signing up?

I create those using the normal disconnected slicers trick:

image

[Conversions per Month]=
[Impressions per Month] * [Conversion Rate]

(I am skipping some steps here – download the workbook at the end of the post to see them).

Accounting for this in a “new customers – hybrid” measure

OK, now I can write a new measure that accounts for ALL new customers in a given month – virally-won customers plus those won over by advertising:

   [New Customers Diminishing – Hybrid] =

   [Initial Customers]*[Diminishing Viral Factor] +
   [Conversions Per Month] +
   IF(MAX(Months[MonthNum])>1,
      [Conversions Per Month]*[ViralFactor]       ,0
     )

Recall that the non-hybrid [New Customers Diminishing] from part two was defined as:

   [Initial Customers]*[Diminishing Viral Factor]

Which is the identical to the first line of the new hybrid measure above.

So in English, our new Hybrid measure is: 

“take the number of new customers we get from diminishing viral spreading, add to that the new customers we get from advertising, and then, if we’re not in month 1, add the customers recruited by last month’s advertising-converted customers.”

Hybrid Total Customers

Continuing the pattern:

[Total Cust – Diminishing Viral plus Advertising (Hybrid)] =

CALCULATE(SUMX(Months, [New Customers Diminishing – Hybrid]),
          ALL(Months),
          FILTER(ALL(Months),
                 Months[MonthNum]<=MAX(Months[MonthNum])
                )
         )
+ [Initial Customers]

This is the same formula as in part two – it just has our Hybrid measure for new customers substituted in.

Displaying for Comparison

I can now place both measures – the Viral Only and the Hybrid – on a single chart for comparison while I manipulate variables via slicers.

Here are a few combinations and the results of each:

image

Start with 5,000 Customers and a 10% Viral Recruitment Factor.  500 Ad Impressions per Month and a 5% Conversion Rate on those Ads Doesn’t Make a Huge Difference.

image

But Reduce Initial Customers to 1,000 and Suddenly, Advertising is Quite Necessary!

image

Revert to 5k Initial Customers But Increase Impressions per Month from 500 to 2k,
and Again, Advertising Earns its Keep.

Rather than post more screenshots, I will post a more attractive version of this spreadsheet to the demo site so you can just try it out.  Check back for a link tomorrow.

Last Observation

The slope of the red line in the graph above is NOT due to advertising conversions alone!

To illustrate, at 2k impressions per month and 5% conversion rate, that means I am adding 100 customers per month from pure advertising.

But the month to month difference in customer count is actually 110 in that chart – the 100 advertising customers plus the 10 customers virally recruited by last month’s advertising customers.

So if you have a better viral factor than 10%, that will boost the blue line for sure, but it will boost the red line even more.  An observation that somehow strikes me as both intuitive and non-obvious at the same time.  This is one of those moments where I learned something about the world, and I really like those moments.

Download the Workbook

Click here to Download

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 One Comment
  1. Is there a way to calculate the sum of future customers? I’m having troubles writing a similar measure. So basically sum of the total customers for each measure from months 2-36, then 3-36, then 4-46, etc.

Leave a Comment or Question