cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IMK Regular Visitor
Regular Visitor

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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
IMK Regular Visitor
Regular Visitor

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

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.

View solution in original post

14 REPLIES 14
Highlighted
Moderator v-qiuyu-msft
Moderator

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

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.
IMK Regular Visitor
Regular Visitor

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

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

IMK Regular Visitor
Regular Visitor

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

How about this?

blm001 Frequent Visitor
Frequent Visitor

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

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

nsnen Visitor
Visitor

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

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

IMK Regular Visitor
Regular Visitor

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

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.

View solution in original post

sushmasushma Regular Visitor
Regular Visitor

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

Hello

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

sushmasushma Regular Visitor
Regular Visitor

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

RespondentsGrades
1303
274
3159
4187
5576
6432
7785
8869
9525
101518

 

Plz tell me How to find NPS for this

BruceRudd Frequent Visitor
Frequent Visitor

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

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,

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 6 members 1,742 guests
Please welcome our newest community members: