cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LeanneShapro
Advocate I
Advocate I

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)

 

 

5 REPLIES 5
Anonymous
Not applicable

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)

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

v-juanli-msft
Community Support
Community Support

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 )

3.png

 

Best Regards

Maggie

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

Johanno
Responsive Resident
Responsive Resident

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?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors