NPS Score needs additional filter

[wmcclure]

Hello all!

I have a DAX formula in place for calculating NPS score based on information that customers provide. I need to add some logic to make sure that NPS is only calculated once per customer assessment (There can be multiple products, so this will duplicate the NPS score for customers that have more than one. 

The current formula is as follows: 

NPS Score =

  VAR ResponseCount = COUNTA(assessment_records[nps_score])

  VAR PromoterCount = COUNTAX(FILTER(assessment_records, [nps_score] >= 9), [nps_score])

  VAR DetractorCount = COUNTAX(FILTER(assessment_records, [nps_score] <= 6), [nps_score])

  VAR Score = 0 + (PromoterCount - DetractorCount) / ResponseCount * 100

  RETURN IF(NOT(ISBLANK(ResponseCount)), Score)

- The logic that needs added is to only count the Non-Blank NPS score ratings once per assessment_records[uuid]

I think I just need to use the "DISTINCT" function but i get errors in the positions that I try to put it in.

Thanks in advance! Let me know if I need to provide more details, but I hope that this is a simple fix that I am just overlooking

[wmcclure]

This is a quick version of the data with the expected results included

uuid	Deal Number	Deal Domain	Customer	Manufacturer	CSAT Score (Manufacturer Score)	NPS Score (Our Company Score)	Prmotor or detractor?
106	124500	Public Cloud	Redacted	Redacted	5	10	p
104	133934	Digital Workspace	Redacted	Redacted	3	10	p
103	134539	Data Center	Redacted	Redacted	3	6	d
103	137171	Data Center	Redacted	Redacted	3	6	DUPLICATE
45	133190	Data Center	Redacted	Redacted	3	10	p
44	133745	Data Center	Redacted	Redacted	4	8
43	129736	Digital Workspace	Redacted	Redacted	3	10	p
43	132535	Digital Workspace	Redacted	Redacted	3	10	DUPLICATE
42	99732	Public Cloud	Redacted	Redacted	5	10	p
42	141020	Enterprise Networking	Redacted	Redacted	5	10	DUPLICATE
41	119798	Public Cloud	Redacted	Redacted	4	10	p
40	132026	Digital Workspace	Redacted	Redacted	5	10	p
Response Count	9
Promotor Count	7
Detractor Count	1
Expected NPS Score	0.67

[Anonymous · Accepted solution]

Hi @wmcclure ,

I apologize for not responding to your message until now.The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure

NPS Score = 
 VAR ResponseCount = COUNTROWS(SUMMARIZE('Table','Table'[uuid]))
 VAR PromoterCount = COUNTROWS(FILTER(SUMMARIZE('Table',[uuid],"Score",MAX('Table'[NPS Score (Our Company Score)])),[Score] >=9))
 VAR DetractorCount = COUNTROWS(FILTER(SUMMARIZE('Table',[uuid],"Score",MAX('Table'[NPS Score (Our Company Score)])),[Score] <= 6))
 VAR Score = IF( ResponseCount >0 ,(0 + (PromoterCount - DetractorCount) / ResponseCount * 100),BLANK())

  RETURN IF(NOT(ISBLANK(ResponseCount)), Score)

2.Final output

[Anonymous]

Hi @wmcclure ,

Regarding your question, try the following expression.

NPS Score =
  VAR _a = COUNTROWS(DISTINCT(assessment_records[uuid]))
  VAR ResponseCount = COUNTA(assessment_records[nps_score])

  VAR PromoterCount = COUNTAX(FILTER(assessment_records, [nps_score] >= 9), [nps_score])

  VAR DetractorCount = COUNTAX(FILTER(assessment_records, [nps_score] <= 6), [nps_score])

 

  VAR Score = IF( _a >0,(0 + (PromoterCount - DetractorCount) / ResponseCount * 100),BLANK())

 

  RETURN IF(NOT(ISBLANK(ResponseCount)), Score)

If my understanding is wrong, please provide simple example data and show the expected result as a picture.

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wmcclure]

Unfortunately, it looks like that has no effect on the result. 

uuid refers to the assessment that is input by our employees. The customer is asked about their individual products as well as about the service that our company provides. When the customer has more than one product that we are asking about, it causes multiple lines of NPS (our company's score) to be in the data. Therefore, we only want to count NPS once per assessment. We arent counting the uuid, we just want to make sure that NPS score isnt counted multiple times for a single uuid entry.