cancel
Showing results for
Did you mean:
Regular Visitor

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

4 REPLIES 4
Member

## Re: Calculate NPS Score with DAX

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?
Community Support Team

## Re: Calculate NPS Score with DAX

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

Regular Visitor

## Re: Calculate NPS Score with DAX

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?

Leanne

Highlighted
Frequent Visitor

## Re: Calculate NPS Score with DAX

My NPS formula:

```NPS Score =