Forum Discussion

wmcclure's avatar
wmcclure
Helper I
2 years ago
Solved

NPS Score needs additional filter

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...
"DAX help"
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
wmcclure
Helper I
2 years ago

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.