Forum Discussion
Weighted NPS Score
I am trying to create a measure that will calculate the weighted NPS score based on contract value (ACV).
I have a list of customers with their survey scores, null survey scores means there has not yet been a response.
I have added a column to find out each customers average score. I want to use this average score to work out the number of detractors and promoters and then factor in the ACV as part of the measure.
Promoters: 9-10
Detractors: 0-6
| Customer | Score | Average Score | ACV |
| A | 10 | 10 | 90,720 |
| B | 10 | 69,738 | |
| B | 10 | 10 | 69,738 |
| C | 6.7 | 122,580 | |
| C | 6.7 | 122,580 | |
| C | 5 | 6.7 | 122,580 |
| C | 7 | 6.7 | 122,580 |
| C | 8 | 6.7 | 122,580 |
| D | 8 | 8 | 16,800 |
| E | 10 | 39,120 | |
| E | 10 | 10 | 39,120 |
In the above data table, the promoters are Customers A,B and E based off their average score.
The Detrator is Customer C.
The measure I want to show is:
(Promoter ACV - Detractor ACV) / Total ACV
((90,720 + 69,738 + 39,120 - 122,580) / 338,958)*100
22.7
Note that each customer ACV should be counted only once.
Thanks
10 Replies
Thanks Ashish but the link to your file seems to be broken. Are you able to repost?
Hi PaulCo,
The link works just fine. Here it is again - https://1drv.ms/u/s!AnsG4LrWCkhUn75DCxXQX3mBOvLliA.
Thanks, this got me there in the end.
Much appreciated.
You are welcome.
Are you calculating average score in PowerBI or it is coming from your data source. Reason I asked, it will be easier if we remove un-wanted rows from table using query editor:
- filter rows where score is blank
- remove score column
- get distinct rows customer|avg score|acv
once above is done, you will have only rows which we are interested to work on, add following measures:
promoter acv = Calculate(Sum(Table[ACV]), Filter(Table, Table[AvgScore]>=9)) detractor acv = Calculate(Sum(Table[ACV]), Filter(Table, Table[AvgScore]<=6)) total acv = Calculate(Sum(Table[ACV]), AllSelected(Table)) NPS = DIVIDE( promoted acv - detractor acv, total acv, 0) * 100
I thought about doing it this way but there are also comments in the surveys that we want to display in a table and if we only keep one record for each customer then we would lose the comments.
Gotcha,
- add new index column in your original table
- you can create another table as suggested in data model, reference original table and keep the original table as it is. set relationship on both tables on this index column
It will get you comments from original table.
