# Creating a formula in Rules Engine based on NPS data by CSM

• Question
• Updated 4 months ago
I am trying to replicate a formula I have in one of my Salesforce reports to calculate the NPS score total by CSM. The end goal is to be able to look at all NPS scores given this QTR, by CSM and have their cumulative score for all of those NPS responses. Though, I am not sure this type of formula is doable in Gainsight.

The formula is:

Count of NPS Promoters/ SUM NPS Responses - Count of NPS Detractors /SUM NPS Responses * 100

Ky Powell, Champion

• 2,594 Points

Posted 4 months ago

Seth, Employee

• 14,480 Points
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.

Ky Powell, Champion

• 2,594 Points
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.

Seth, Employee

• 14,480 Points
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" :-)

Ky Powell, Champion

• 2,594 Points
Awesome, I will try this! Thank you Seth!

Dan Ahrens, Official Rep

• 24,802 Points
You can write a rule to do this.

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

Ky Powell, Champion

• 2,594 Points
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.
(Edited)

Dan Ahrens, Official Rep

• 24,802 Points
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...