Forum Discussion

LeanneShapro's avatar
LeanneShapro
Icon for Advocate I rankAdvocate I
8 years ago

Calculate NPS Score with DAX

Hello Power BI Users - 

 

I use the following excel formula to calculate my NPS score and I'd like to create the DAX equivalent. It should take the percentage of promoters (scores of 9 and 10) minus the percentage of detractors (scores of 0 to 6) What do you suggest? 

 

NPS Excel formula -
=ROUNDUP((100*((COUNTIF(A:A,">8")-COUNTIF(A:A,"<7"))/COUNT(A:A))),0)

 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    My NPS formula:

    NPS Score =
  VAR ResponseCount = COUNTA(SurveyResponse[Answer])
  VAR PromoterCount = COUNTAX(FILTER(SurveyResponse, [Answer] >= 9), [Answer])
  VAR DetractorCount = COUNTAX(FILTER(SurveyResponse, [Answer] <= 6), [Answer])

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

  RETURN IF(NOT(ISBLANK(ResponseCount)), Score)
    • rayinOz's avatar
      rayinOz
      Icon for Helper III rankHelper III
      5 years ago

      Hello

       

      Thanks for the NPS formula. In my table I have different course names [module] which is text. How do I add in a filter so the NPI score is filtered by course names? 

       

      Thanks!

      Ray

  • Johanno's avatar
    Johanno
    Icon for Continued Contributor rankContinued Contributor
    8 years ago
    Hi, I would have created a calculated column using SWITCH so that each row get either promoter, detractor or blank. Then use a measure to count and divide. How does that sound?
  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi LeanneShapro

    You could create measures

    Measure = CALCULATE(COUNT(Table1[name]),FILTER(ALL(Table1),[score]>8))

Measure 2 = CALCULATE(COUNT(Table1[name]),FILTER(ALL(Table1),[score]<7))

Measure 3 = CALCULATE(COUNT(Table1[name]),ALL(Table1))

Measure 4 = ([Measure]-[Measure 2])/[Measure 3]

Measure 5 = ROUNDUP([Measure 4],0)

    Or a nested measure

    Measure 6 =
VAR low =
    CALCULATE ( COUNT ( Table1[name] ), FILTER ( ALL ( Table1 ), [score] < 7 ) )
VAR high =
    CALCULATE ( COUNT ( Table1[name] ), FILTER ( ALL ( Table1 ), [score] > 8 ) )
VAR alname =
    CALCULATE ( COUNT ( Table1[name] ), ALL ( Table1 ) )
VAR percentage = ( high - low )
    / alname
RETURN
    ROUNDUP ( percentage, 0 )

     

    Best Regards

    Maggie

    • LeanneShapro's avatar
      LeanneShapro
      Icon for Advocate I rankAdvocate I
      8 years ago

      Hi v-juanli-msft - 

       

      I appreciate you sharing your recommendation. 

       

      I entered the following formula per your suggestion: 

       

      NPS = VAR low =
      CALCULATE ( COUNT ( 'GetFeedback Result'[Likely to Recommend - RNPS] ), FILTER ( ALL ( 'GetFeedback Result' ), [Likely to Recommend - RNPS] < 7 ) )
      VAR high =
      CALCULATE ( COUNT ( 'GetFeedback Result'[Likely to Recommend - RNPS] ), FILTER ( ALL ( 'GetFeedback Result' ), [Likely to Recommend - RNPS] > 8 ) )
      VAR alname =
      CALCULATE ( COUNT ( 'GetFeedback Result'[Likely to Recommend - RNPS] ), ALL ( 'GetFeedback Result' ) )
      VAR percentage = ( high - low )
      / alname
      RETURN
      ROUNDUP ( percentage, 0 )

       

      When I added the measure as a Card on my dashboard, it came out as a -1. When I used my excel formula, it resulted the value -33, which is accurate. 

       

      Do you have further recommendations for me? 

       

      Thanks in advance, 

       

      Leanne