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


Net Promoter Score in Power Pivot

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. 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:

Our Critical Question/Column for Net Promoter Purposes is Pretty 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…

Net Promoter Score in Power Pivot

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:

  1. 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.
  2. [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!

Net Promoter Score in Power Pivot - Sliceable Without Formula Surgery!

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 Smile


OK Maybe a Few of Them Are Made Up.

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 9 Comments
  1. If this table was ginormous, that calculated column would bog down the model, right? If so, then would importing the table into Power Pivot via Power Query and inserting a custom column for Cleaned Overall Score keep the model from getting sluggish?

    I have about zero experience with M formulas. I didn’t get very far trying to recreate Clean Overall Score in M:
    =Text.Range([Overall Quality of Your Experience], 0, 2)

    I briefly looked for If Error in M and decided I should quit procrastinating and work on my to do list.

    1. In general, calc columns can get “heavy” in big tables, yes. But for survey data, it would be hard to get into the millions of rows required to make a difference.

      Plus the formula in question doesn’t do any sophisticated scans/searches/filters – it purely looks at just the current row, so you don’t get the RAM/CPU hit while the calculation runs, either.

      So in short I would not worry about it this time.

      Also – in terms of file weight and RAM consumption, the Comment column would become a problem LONG before this calc column.

  2. I’m not following. From the very first illustration, the calculation seems to be wrong. If there are 12 Promoters, 5 Detractors, and 23 respondents in total, NPS is 30%, not 52% as stated. Where is the 52% coming from? This would give a company a very inflated score.

    1. Good catch. I made that screenshot BEFORE I fixed a formula bug. All of the formulas in the post are correct, as is the downloadable workbook.

      But the old screenshot lingered. Fixed now. Sorry for the confusion.

  3. Tsk tsk

    It’s Bill S. Preston, Esq. and Ted “Theodore” Logan. — together, they are Wyld Stallyns!!

    I’m chalking it up to last minute book anxiety.

    I think Keanu’s better sounding “whoa” came in The Matrix — which I think today is the 15 year anniversary?

  4. I really enjoyed this post and it was very timely for me as we are currently building out our NPS scorecard in PowerView using Sharepoint 2013 with a PowerPivot model backing it up. It was almost like you were writing for me. 🙂

    One slight bit of feedback though, having read the Ultimate Question 2.0 and now considering myself an expert worthy of making this comment (laugh here), I have an issue with the formulas. The NPS is supposed to be % of promoters – % of detractors and not the quantity. Here is how we structured ours:

    Num of Surveys Received:=DISTINCTCOUNT([Surveys.Survey Number]))
    This is because we sometimes get dups when the user clicks twice.

    Percentage of Promoters:=[Num of Promoter]/[Num of Survey Received]
    Percentage of Detractors:=[Num of Detractor]/[Num of Survey Received]
    NPS:=[Percentage of Promoters] – [Percentage of Detractors]

    Hope this helps someone else.

    Your blog is the MOST useful thing to me as I have been learning PowerPivot. I bought the book as a result and consumed it in one evening. I bought another for a coworker and now have a partner in crime. Here’s to changing the face of information in my department!

    1. Hi Mike! I’m SUPER glad to hear that the site (and the book) are turning out to be helpful to you 🙂

      With regard to the “pct vs. quantity” difference, the results will be the same most of the time right? EX: 50% – 40% = 10% vs. (30 – 24) / 60 = 10%

      If the sample sizes are different however (30 out of 60 are promoters, but 25 out of 50 are detractors) then we’d have to do the percentages first. But would the “official” NPS methodology frown upon differing sample sizes?

  5. Hi, thanks for this article. I am looking at creating a spreadsheet to calculate the individual NPS of skippers for a yacht charter company. Do you have a formula that allows for filters so I can calculate the NPS per skipper? Thanks a lot. JK

  6. I echo the thanks for your blog and books. I’m going to build on what you’ve put here to build a model in which I can trend the NPS over months and quarters and slice by various customer segments.

Leave a Comment or Question