Ky, prepare yourself, some algebra is about to blow your mind (as it did mine):





Check out all the responses to Scott Morris' comment on this post: https://community.gainsight.com/gainsight/topics/true-nps-calculation-and-nps-widget





(You'll likely need to click the "View previous..." link to expand them all.)





In short, you're going to create a basic calculated field on the NPS response object, and then simply do an Average on that field in the Rule.

---

Thanks Seth, so the issue here is getting the actual accurate NPS Scores requires a specific NPS formula. I can't just take the average, which is why I need to be able to replicate that formula exactly. 

---

Hi Ky -- the trick that I discovered through those comments is that the algebra works out identically, if you give the promoters a score of 100, the detractors a score of -100, the passives a score of 0, and average those scores together. You'll get the same NPS value per CSM as if you'd done the calculation shown in your screenshot.





I mean, you could literally put the equations on opposite sides of an equals sign, and do the algebraic reduction until you end up with a formula of "1=1" 🙂

---

Awesome, I will try this! Thank you Seth!

---

You can write a rule to do this. 

Use a pivot task to bucket your responses:

And then a formula field in the transform task using the output fields you created in the pivot task:

---

Thanks Dan! This is exaclty what I was looking for that I thought I could do. Though, when I am doing my pivot task, it makes me choose a field to group by and then aggregates my show me fields. How can I avoid this?

Also, I have created everything that you had suggested. Though, my end goal is to use this in reporting. So that I look at that final value I created from the formula field by each CSM. Though, my first thoughts were to Load this to a new field in SFDC, but that won't work if I am grouping this by CSM in my rule. Not sure how to get the results from my rule out in the way that I can use it in reporting to look at the Total NPS Score by CSM for all of their NPS's created this QTR. 

---

Hi Ky,





If you want to use this to report on NPS by CSM, this is how the pivot would look:

What you'd want to do in order to report on this is to write it to a custom MDA table that would show the NPS score by CSM. 





Here's help on creating a new MDA object: https://support.gainsight.com/Product_Documentation/Data_Management_and_Integrations/Managing_Data_I...

---