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 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

"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

     

4 Replies

  • 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.

  • wmcclure's avatar
    wmcclure
    Helper I
    2 years ago

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

    uuidDeal NumberDeal DomainCustomerManufacturerCSAT Score (Manufacturer Score)NPS Score (Our Company Score)Prmotor or detractor?
    106124500Public CloudRedactedRedacted510p
    104133934Digital WorkspaceRedactedRedacted310p
    103134539Data CenterRedactedRedacted36d
    103137171Data CenterRedactedRedacted36DUPLICATE
    45133190Data CenterRedactedRedacted310p
    44133745Data CenterRedactedRedacted48 
    43129736Digital WorkspaceRedactedRedacted310p
    43132535Digital WorkspaceRedactedRedacted310DUPLICATE
    4299732Public CloudRedactedRedacted510p
    42141020Enterprise NetworkingRedactedRedacted510DUPLICATE
    41119798Public CloudRedactedRedacted410p
    40132026Digital WorkspaceRedactedRedacted510p
            
    Response Count9     
    Promotor Count7     
    Detractor Count1     
            
    Expected NPS Score                                     0.67     
    • Anonymous's avatar
      Anonymous
      Not applicable
      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