Net Promoter Scores Are Fiendishly Simple to Calculate in Power Pivot
What is “Net Promoter Score?”
Fundamentally, it’s a measure of how many of your customers love you, minus how many of them dislike you. Hence the name – Net Promoter Score.
WARNING: I am personally no expert here. I am doing my usual thing: take a small amount of knowledge and wield it like a battle axe. I was helping a client today (Monday) with this, and am writing about it a mere three hours later. But I figure there are lots of people out there who need to do this sort of thing, and THEY get what it all means. So allow me to share how EASY these calcs are in Power Pivot.
NetPromoter.com describes NPS as:
…based on the fundamental perspective that every company’s customers can be divided into three categories: Promoters, Passives, and Detractors…
…To calculate your company’s NPS, take the percentage of customers who are Promoters and subtract the percentage who are Detractors.
Aside: for about six months back in 2006-2007, my friend Jeff Tran at Microsoft walked around spouting the term “Net Promoter Score” so often that it basically became a punctuation mark. “Hey Rob, let’s get tacos for lunch today? Net Promoter.” I never bothered to understand what the heck he meant by it. Of course, Jeff is now Director of Partnerships at Microsoft, for, oh, um, the National Freaking Football League, and posts pictures of himself with Roger Goodell a couple times a month. Which is, you know… quite a net promotion! Oh man I’m funny. Hmm, back to the post.
Enter: A Hyper-Realistic Survey Data Set!
Yes, they are all fictional characters. No, the comments have ZERO analysis value.
Yes, coming up with this data set took me half an hour.
Let’s add some challenge…
Really, for NPS purposes, we only care about this one survey question, and it’s… noisy:
So let’s add a calc column that cleans that mess up a bit, shall we?
[Clean Overall Score] – Formula Below
[Clean Overall Score] =
IFERROR(INT(LEFT([Overall Experience],2)),BLANK())
OK, that gives us a fighting chance.
Now, Time for Measures!
Good news for sure! The formulas here are pretty simple compared to some of the things appearing on this site lately…
Counting Respondents, and Filtering Out “Skips”
[Respondents]:=
COUNTROWS(Surveys)
[Respondents Answering the Question]:=
CALCULATE([Respondents],
NOT(ISBLANK(Surveys[Clean Overall Score]))
)
Counting Promoters and Detractors
[Promoters]:=
CALCULATE([Respondents],
Surveys[Clean Overall Score]>=9
)
[Detractors]:=
CALCULATE([Respondents],
Surveys[Clean Overall Score]<=6,
Surveys[Clean Overall Score]>=1
)
Two notes here:
- I based these measures off of [Respondents] rather than [Respondents Answering the Question] Logically, you’d think I’d get the same answer either way. But nope, I didn’t. There’s something weird about that NOT(ISBLANK()) test in the [Respondents Answering the Question] measure that makes it do weird things when you filter it again in subsequent CALCULATE’s.
- [Detractors] needs that “<=1” test, otherwise we’d count the “skips” as Detractors. This is because blanks are mathematically equivalent to 0, and therefore less than 6.
Now the “Conclusion” Measures
[Net Promoters]:=
[Promoters] – [Detractors]
[% Net Promoters]:=
DIVIDE([Net Promoters], [Respondents Answering the Question])
Why is this better than normal Excel?
For the same reason as usual: we wrote these formulas ONCE, and now we theoretically never need to write them again.
Want to know how our NPS varies between Male and Female respondents? No need to adjust your formulas, just slice it!
No, I am NOT Attempting a Statement Here. All Data Courtesy of RANDBETWEEN().
And if we had dates on these surveys, we could rearrange the pivot and quickly be looking at trends over time. Again, no formula surgery – just a few clicks.
Portability. It’s the new Black. Black magic, that is. Black data magic, I mean.
Download the Workbook!
Please, please download the workbook. I mean, I made more fake quotes than fit in screenshots and I don’t want to face up to that effort as the wasted time that it was 😉
Click here to download the workbook
Oh you didn’t download it? OK fine, here are the quotes.
You’re just so stubborn aren’t you? Well so am I, so here you go 
OK Maybe a Few of Them Are Made Up.
