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!
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] =
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 Answering the Question]:=
NOT(ISBLANK(Surveys[Clean Overall Score]))
Counting Promoters and Detractors
Surveys[Clean Overall Score]>=9
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
[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 😉
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.