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!
Picking up from last week’s post, the first thing I want to show is that I kinda cheated last time. To see what I mean, let’s look at Rahul’s original chart:
In Rahul’s Viral Model, Total Customers “Goes Flat” Quickly
In Rahul’s model, if we start With 5,000 initial customers and a viral factor of 0.2, we end up with 6,250 customers and we never get any more!
But in my model from last week, if I use 5,000 and 0.2, customers keep piling up exponentially:
In My Model from Last Week, Customers Never Go Flat –
They Just Keep Growing Exponentially
So why the difference?
“Customers Recruited per Month” versus “Customers Recruited Ever”
In Rahul’s model, a viral factor of 0.2 means that each existing customer will, on average, recruit 0.2 additional customers over the lifetime of that existing customer.
Rahul’s Model Example: If I look at an average group of five customers, over even a 36-month period of time, those five will recruit one new additional customer, total.
In my model, each existing customer recruits 0.2 additional new customers every single month.
Rob’s Model Example: Five average customers over a 36 month period will recruit 5 * 36 * 0.2 = 90 customers.
One customer versus 90. That’s a big difference
Which One is More Correct? In Short, Rahul!
While Rahul’s approach may seem counterintuitive on first glance, I think his approach makes more sense. Over a long enough timeframe, my model predicts that you end up with more customers than there are human beings on the planet. Uncapped exponential growth is impossible – there is always a limit.
Facebook, for instance, currently reaches about 70% of people living in the US and Canada. That number is very unlikely to ever reach, say, 200%, so it is natural to see it leveling off:
Still Growing Sharply in the Rest of the World, But US and Canada Are Leveling Off
Everything, even something as ubiquitous as Facebook, eventually finds its max saturation point. So we should be very distrustful of models like last week’s, which never level off.
If you’re still uncomfortable with the correctness of Rahul’s approach, see the end of this post where I explain further. For now though let’s focus on getting a measure that matches Rahul’s chart.
In Rahul’s Model, Only New Customers Can Recruit!
The key observation for me, when I was figuring this out, was this: in Rahul’s model, only the newly-recruited customers can recruit others. In other words, all of those existing customers already had their chance to recruit someone, and we’ve added all of their recruits already:
In Rahul’s Model, Growth (New Customers) are 20% of Last Month’s Growth,
NOT 20% of Current Customers)
DIMINISHING Viral Factor!
So the first thing I needed was a measure that DIMINISHES in value at each step – where each month is 20% as large as the prior month.
Diminishing Viral Factor – Each Step is 20% Smaller Than the Last
(Or Whatever Viral Factor You Select)
The formula for this is:
[Diminishing Viral Factor] =
Which is a lot like my [Ongoing Viral Factor] measure from the prior post. The biggest difference is that in the Ongoing measure, it’s 1 + [Viral Factor] being raised to a power, whereas here, it’s just [Viral Factor], which is always less than one.
New Customers Diminishing
OK, now a simple multiplication gives me my new customers at each step:
[New Customers Diminishing]=
[Initial Customers]*[Diminishing Viral Factor]
1,000 drops to 200, to 40, to 8… Just Like Rahul’s Model
Adding Up New Customers Over Time
OK, now all I need to do is add up those [New Customers Diminishing] values over time:
[Customers – Diminishing Viral] =
CALCULATE(SUMX(Months, [New Customers Diminishing]),
+ [Initial Customers]
Translated into English, that formula above says “sum up all [New Customers Diminishing] values for all of the months before this one, then add my [Initial Customers].” Conceptually pretty simple. But it took me a long time to figure it out.
King Kong meets Godzilla. I’m grateful to have an acceptable public outlet for this stuff
OK, Does it Flatten Out?
Yes. Yes it does:
It’s a little easier to see with higher viral factors, so let’s try 80% for comparison:
I’ve developed an obsession with this model, so I’m going to revisit it on Thursday
Because “viral spreading” has this nasty habit of flattening out rapidly, it’s important to also complement it with traditional marketing. So I’m going to factor that in.
And I’m also going to publish the finished model to the demo site, so that you can play with it interactively.
If You Are Interested: Objections to Rahul’s Approach
The most counterintuitive thing about this model is the notion that “I cannot recruit anyone after my first month.” That just isn’t true in real life. For instance, I didn’t introduce PowerPivot to one person and then quit – I keep “recruiting” more people as time goes on.
I don’t think Rahul’s approach ignores that though. I think he factors that into determining his viral factor. For instance, if we surveyed 100 people who’ve been using PowerPivot for a year, and asked them how many folks they’ve “recruited,” we’d get an answer somewhere around 30-40 I think, and that would be fair to assign a viral factor of 0.3 or 0.4 and run with that. (We would then need to use Years rather than Months as our “time step,” but you get the idea).
And in an Internet business like Rahul’s, what happens 36 months from now is something to worry about once you’ve become very successful. What happens over the next 6 months determines whether you will be successful.
In other words he is measuring the “boost phase” of his business. This isn’t an exact science – being close is good enough. And I think his model captures that.