Forum Discussion
NPS Calculation in BI
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.
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 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.
9 Replies
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 )disregard, I found it!! thanks again
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.
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 )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 )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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
