Weighted NPS Score

[PaulCo]

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

[Ashish_Mathur · Accepted solution]

Hi @PaulCo,

I believe the detractor average scroe should be 0-7 (not 0-6).  You may refer to my solution in this file.

Hope this helps.

[PaulCo]

Thanks, this got me there in the end.

Much appreciated.

[Ashish_Mathur]

You are welcome.

[PaulCo]

Thanks Ashish but the link to your file seems to be broken. Are you able to repost?

[Ashish_Mathur]

Hi @PaulCo,

The link works just fine.  Here it is again - https://1drv.ms/u/s!AnsG4LrWCkhUn75DCxXQX3mBOvLliA.

[parry2k]

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

[PaulCo]

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.

[parry2k]

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. 

[PaulCo]

I think that should work.

However, I calculated the average score using the following expression under the New Column feature on Power BI Desktop.

Average Customer Score = 
	CALCULATE(
		AVERAGE('Table'[Survey Score]),
		ALLEXCEPT('Table','Table'[Customer])
		)

How do I recreate this in the query editor so that I can use the field when once I have removed the duplicates? 

[parry2k]

This is a DAX expression and not to use in Query Editor. Once you are done in query editor, add new MEASURE.