- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Power BI 中文博客
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Trying to calculate NPS (Net Promoter Score) v...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Trying to calculate NPS (Net Promoter Score) via custom calculated column

03-13-2017
02:45 AM

Hi

I am trying to find a correct formula syntax to calculate NPS score (Net Promoter Score).

I know how the formula goes in paper but I can't figure out the correct syntax to place in the custom calculated column.

Below I will write the calculation. I would appreciate if someone could help me to find the correct formula syntax.

I have a column that contains grades given by customers from 0-10. There may be some row's where there is no grade and these row's should not be in the overall calculation.

NPS Score Calculation:

**Example:** If you received 100 responses to your survey:

10 responses were in the 0–6 range (Detractors)

20 responses were in the 7–8 range (Passives)

70 responses were in the 9–10 range (Promoters)

(Number of Promoters — Number of Detractors) / (Number of Respondents) x 100

When you calculate the percentages for each group, you get 10%, 20%, and 70% respectively.

To finish up, subtract 10% (Detractors) from 70% (Promoters), which equals 60%. Since an example Net Promoter Score is always shown as just an integer and not a percentage, your NPS is simply 60. (And yes, you can have a negative NPS, as your score can range from -100 to +100.)

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-23-2017
10:15 PM

I have finally got a working syntax for my needs..

Calculating NPS Net Promoter Score using Power BI:

When the Column with NPS grades are named "Grades", following works:

Modeling -tab -> New Column

Column = IF ( ISBLANK ( 'Table1'[Grades] ); "Tyhja"; SWITCH ( TRUE (); 'Table1'[Grades] >= 0 && 'Table1'[Grades] <= 6; "Detractors"; 'Table1'[Grades] = 7 || 'Table1'[Grades] = 8; "Passives"; 'Table1'[Grades] = 9 || 'Table1'[Grades] = 10; "Promoters" ) )

Modeling -tab -> New Measure

NPS = var NumOfRespon= CALCULATE ( COUNTA ( Table1[Grades] ); FILTER ( Table1; Table1[Grades] <> BLANK () || Table1[Grades] = 0 ) ) return (CALCULATE(COUNTA(Table1[Respondents]),FILTER('Table1','Table1'[Column]="Promoters"))/NumOfRespon-CALCULATE(COUNTA(Table1[Respondents]),FILTER('Table1','Table1'[Column]="Detractors"))/NumOfRespon) *100

This syntax will ignore all empty cells (rows) in the grades column and do not include empty's in the NumOfRespon which is used as divider for the final NPS score.

16 REPLIES 16

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-18-2019
09:29 AM

I suggest looking at the following article https://www.displayr.com/nps-recoding/ where it basically says to create a column with values -100, 0 or 100 depending if the score on the surveys corresponds to a detractor, promoter o passive. Then all you have to do is agregate that column by using the mean and you will always get the correct result.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-17-2020
02:14 AM

Ingenious solution!

Any other method of doing it is simply wrong compared to the simplicity here

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-14-2017
12:59 AM

Hi @IMK,

You can create a calculated column to return Detractors, Passives, Promoters values in a column, then create a measure return NPS:

Column = SWITCH(TRUE(),'Table1'[Grades]<=6,"Detractors",'Table1'[Grades]=7 ||'Table1'[Grades]=8, "Passives",'Table1'[Grades]=9 || 'Table1'[Grades]=10,"Promoters")

NPS = var NumOfRespon= CALCULATE(COUNTROWS('Table1'),ALL('Table1')) return (CALCULATE(COUNTA(Table1[Respondents]),FILTER('Table1','Table1'[Column]="Promoters"))/NumOfRespon-CALCULATE(COUNTA(Table1[Respondents]),FILTER('Table1','Table1'[Column]="Detractors"))/NumOfRespon) /NumOfRespon *100

Please take a look at attached .pbix file.

Best Regards,

Qiuyun Yu

Community Support Team _ Qiuyun Yu

If this post**helps**, then please consider *Accept it as the solution** to help the other members find it more quickly.*

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-18-2018
09:53 PM

Respondents | Grades |

1 | 303 |

2 | 74 |

3 | 159 |

4 | 187 |

5 | 576 |

6 | 432 |

7 | 785 |

8 | 869 |

9 | 525 |

10 | 1518 |

Plz tell me How to find NPS for this

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-23-2017
01:36 AM

Thanks Qiuyun Yu, very helpful - I was just looking for a NPS solution too. I think this needs one correction though, as it is not returning the right value (NP Score from the given data should be 60, not 6). If you remove the last '/NumOfRespon' from the measure formula it works fine. Like this:

NPS = var NumOfRespon = CALCULATE(COUNTROWS('Table1'), ALL('Table1')) return (CALCULATE(COUNTA(Table1[Respondents]), FILTER('Table1', 'Table1'[Column]="Promoters")) / NumOfRespon - CALCULATE(COUNTA(Table1[Respondents]), FILTER('Table1', 'Table1'[Column]="Detractors")) / NumOfRespon) * 100

BR,

Niina

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-23-2017
10:15 PM

I have finally got a working syntax for my needs..

Calculating NPS Net Promoter Score using Power BI:

When the Column with NPS grades are named "Grades", following works:

Modeling -tab -> New Column

Column = IF ( ISBLANK ( 'Table1'[Grades] ); "Tyhja"; SWITCH ( TRUE (); 'Table1'[Grades] >= 0 && 'Table1'[Grades] <= 6; "Detractors"; 'Table1'[Grades] = 7 || 'Table1'[Grades] = 8; "Passives"; 'Table1'[Grades] = 9 || 'Table1'[Grades] = 10; "Promoters" ) )

Modeling -tab -> New Measure

NPS = var NumOfRespon= CALCULATE ( COUNTA ( Table1[Grades] ); FILTER ( Table1; Table1[Grades] <> BLANK () || Table1[Grades] = 0 ) ) return (CALCULATE(COUNTA(Table1[Respondents]),FILTER('Table1','Table1'[Column]="Promoters"))/NumOfRespon-CALCULATE(COUNTA(Table1[Respondents]),FILTER('Table1','Table1'[Column]="Detractors"))/NumOfRespon) *100

This syntax will ignore all empty cells (rows) in the grades column and do not include empty's in the NumOfRespon which is used as divider for the final NPS score.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-07-2019
05:19 PM

What is Table1[Respondents] here? It is not referenced anywhere else

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-01-2019
07:06 AM

I've been using this and it does give me the correct NPS score but as I looked at the filter again today wouldn't it be filtering out all the '0' scores that were given from the NumOfRespon? As the NPS scale is 0-10, 0 is a valied response that should be included.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-22-2019
03:52 AM

Based on your formula, I've created an alternative that handles blanks correctly in the case where there are only Passives and no Promoters or Detractors.

NPS Score = VAR ResponseCount = COUNTA(SurveyResponse[Answer]) VAR PromoterCount = COUNTAX(FILTER(SurveyResponse, [Answer] >= 9), [Answer]) VAR DetractorCount = COUNTAX(FILTER(SurveyResponse, [Answer] <= 6), [Answer]) VAR Score = 0 + (PromoterCount - DetractorCount) / ResponseCount * 100 RETURN IF(NOT(ISBLANK(ResponseCount)), Score)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

08-29-2018
01:53 PM

I had to make some changes to get this to work. First, all the semi-colons in the column creation code needed to be changed to commas. Second, in the code to create the NPS measure I had to change Table1[Respondants] to be the column that had my individual NPS scores. I think in the original example, this would have been the Grades column, not the Respondants column which I could find no reference to in this example. But, the logic seems to be sound. Here is what I neded up with:

Column 2 = IF ( ISBLANK ( 'Sheet1'[Brand NPS] ), "Tyhja", SWITCH ( TRUE (), 'Sheet1'[Brand NPS] >= 0 && 'Sheet1'[Brand NPS] <= 6, "Detractors", 'Sheet1'[Brand NPS] = 7 || 'Sheet1'[Brand NPS] = 8, "Passives", 'Sheet1'[Brand NPS] = 9 || 'Sheet1'[Brand NPS] = 10, "Promoters" ) )

And

NPS = var NumOfRespon= CALCULATE ( COUNTA ( Sheet1[Brand NPS] ), FILTER ( Sheet1, Sheet1[Brand NPS] <> BLANK () ||Sheet1[Brand NPS] = 0 ) ) return (CALCULATE(COUNTA(Sheet1[Brand NPS]),FILTER('Sheet1','Sheet1'[Column 2]="Promoters"))/NumOfRespon-CALCULATE(COUNTA(Sheet1[Brand NPS]),FILTER('Sheet1','Sheet1'[Column 2]="Detractors"))/NumOfRespon) *100

Note, that in my spreadsheet my individual NPS scores were the the Brand NPS column of Sheet1. To use this example, you will need to change yours to match the name of the column you want to perform the NPS calculation on.

Good luck,

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-30-2018
03:53 AM

It's weird. For a long time this worked fine but now all my reports say: "The syntax for '"Detractors"' is incorrect." Any chance someone knows what's going on?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

10-30-2018
03:51 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-18-2018
08:37 PM

Hello

Can u tell me what is "Tyhja" means in ur formula

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-18-2017
08:23 AM

I must have mis-typed something because I could not get it to work. But by trying to understand what each part did, I eventually came up with a less elegant solution. So thanks Qiuyun Yu-

NPS = ((calculate(counta('R&R Data'[Ticket Number]),filter('R&R Data','R&R Data'[Survey Type]="Promoter"))- calculate(counta('R&R Data'[Ticket Number]),filter('R&R Data','R&R Data'[Survey Type]="Detractor"))) /

(calculate(counta('R&R Data'[Ticket Number]),filter('R&R Data','R&R Data'[Survey Type]="Promoter"))+

calculate(counta('R&R Data'[Ticket Number]),filter('R&R Data','R&R Data'[Survey Type]="Detractor"))+

calculate(counta('R&R Data'[Ticket Number]),filter('R&R Data','R&R Data'[Survey Type]="Passive"))))

*100

"Ticket Number" is number of responses and my data already had each rows Detractor, Passive or Promoter Type defined in column "Survey Type".

Regards,

Bruce

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-17-2017
09:20 AM

Thanks! Does this formula leave out row's with empty cell's so that they are not in the calculation at all?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

03-21-2017
05:46 AM

How about this?