NPS Calculation in BI

[jcastr02]

I am trying to find a DAX Measure to calculate the NPS score.  This is the way we calculate it :  1.  Not a %  2.  May be a negative number as well.  

(% of 9's + 10's)-(% of 0's thru 6's)    Below is sample data "likely to recommend"  and sample of how we calculate NPS

I would appreciate help.

[d_gosbell · Accepted solution]

You should be able to do this with something like the following:

NPS Score =
VAR _advocates = CALCULATE( COUNTROWS(Table), Table[Likely to Recommend] >= 9 )
VAR _detractors = CALCULATE( COUNTROWS(Table), Table[Likely to Recommend] >= 9 )
VAR _totalResponses = CALCULATE( COUNTROWS(Table ) )
RETURN DIVIDE( _advocates - _detractors, _totalResponses )

[dax]

Hi jcastr02,

You could refer to  above suggestions or you also could refer to my sample to see whether it work or not. If you want to set color, you could use conditional formatting.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[d_gosbell · Accepted solution]

You should be able to do this with something like the following:

NPS Score =
VAR _advocates = CALCULATE( COUNTROWS(Table), Table[Likely to Recommend] >= 9 )
VAR _detractors = CALCULATE( COUNTROWS(Table), Table[Likely to Recommend] >= 9 )
VAR _totalResponses = CALCULATE( COUNTROWS(Table ) )
RETURN DIVIDE( _advocates - _detractors, _totalResponses )

[jcastr02]

@d_gosbell   How can we make it if, the NPS Score is 0 to show 0.  Right now if the result is 0, the visual shows (blank).  

[d_gosbell · Accepted solution]

---

@jcastr02 wrote:

How can we make it if, the NPS Score is 0 to show 0.  Right now if the result is 0, the visual shows (blank).  

---

Typically that would mean that the score is actually blank, but it is annoying how card visuals do not give you the option to display a 0 instead. 

What I tend to do is to create a measure specially for cards that has something like =IF( ISBLANK( [NPS Score] ), 0 , [NPS Score] ) 

Generally this is not a good pattern as it forces a non-blank result which can hurt performance, but for visuals like cards it's OK.

[jcastr02]

@d_gosbell Thanks so much!

[jcastr02]

@d_gosbell this worked, thanks so much.  I am trying to just make it show as a whole number.  I was playing with the formula to just multiply by 100 to move decimal over but keep getting errors.  Any ideas?  

[jcastr02]

disregard, I found it!! thanks again 

[d_gosbell]

You should be able to do this with something like the following:

NPS Score =
VAR _advocates = CALCULATE( COUNTROWS(Table), Table[Likely to Recommend] >= 9 )
VAR _detractors = CALCULATE( COUNTROWS(Table), Table[Likely to Recommend] >= 9 )
VAR _totalResponses = CALCULATE( COUNTROWS(Table ) )
RETURN DIVIDE( _advocates - _detractors, _totalResponses )

[d_gosbell]

You should be able to do this with an expression like the following:

NPS Score=
VAR _advocates = CALCULATE( COUNTROWS( Table ) , Table[Likely to Recommend] >= 9)
VAR _detractors = CALCULATE( COUNTROWS( Table ) , Table[Likely to Recommend] <= 6)
VAR _totalResponses = CALCULATE( COUNTROWS( Table ) )
RETURN DIVIDE(  _advocates - _detractors, _totalResponses )