Forum Discussion

aggarwal25's avatar
aggarwal25
Frequent Visitor
8 years ago
Solved

NPS calculation at Company level

Hi All,

 

I am calculating NPS using the follwing:

Relationship NPS = var NumOfRespon= CALCULATE (
COUNTA ('Survey Response'[Relationship Likely to Recommend-Value]),FILTER('Survey Response', 'Survey Response'[Relationship Likely to Recommend-Value] <> BLANK() || 'Survey Response'[Relationship Likely to Recommend-Value] = 0 )
)
return
(CALCULATE(COUNTA('Survey Response'[Survey Name]),FILTER('Survey Response','Survey Response'[Relationship NPS Distribution]="Promoters"))/NumOfRespon-CALCULATE(COUNT('Survey Response'[Survey Name]),FILTER('Survey Response','Survey Response'[Relationship NPS Distribution]="Detractors"))/NumOfRespon) *100

 

This works fine if I apply to full data set. But i have data with a list of companies and multiple respondents from same company. If for example, in a  company 3 people responded and all are passives (i.e. rated 7 or 8), then the NPS should be 0, but with the above formula it returns 'Blank'. 

 

Please help, how can i modify this mesaure or suggest another way to calculate the same.

 

Thanks

Need Help
Show and Tell
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    Really difficult to decipher what you are doing there, but if sometimes you return a blank when you really want to return 0, then this should work:

     

    Relationship NPS = 
var NumOfRespon= 
CALCULATE (
	COUNTA ('Survey Response'[Relationship Likely to Recommend-Value]),
	FILTER('Survey Response', 'Survey Response'[Relationship Likely to Recommend-Value] <> BLANK() || 'Survey Response'[Relationship Likely to Recommend-Value] = 0 )
)

VAR thebombdiggedty = (CALCULATE(COUNTA('Survey Response'[Survey Name]),FILTER('Survey Response','Survey Response'[Relationship NPS Distribution]="Promoters"))/NumOfRespon-CALCULATE(COUNT('Survey Response'[Survey Name]),FILTER('Survey Response','Survey Response'[Relationship NPS Distribution]="Detractors"))/NumOfRespon) *100

RETURN IF(ISBLANK(thebombdiggedty),0,thebombdiggedty)

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Really difficult to decipher what you are doing there, but if sometimes you return a blank when you really want to return 0, then this should work:

     

    Relationship NPS = 
var NumOfRespon= 
CALCULATE (
	COUNTA ('Survey Response'[Relationship Likely to Recommend-Value]),
	FILTER('Survey Response', 'Survey Response'[Relationship Likely to Recommend-Value] <> BLANK() || 'Survey Response'[Relationship Likely to Recommend-Value] = 0 )
)

VAR thebombdiggedty = (CALCULATE(COUNTA('Survey Response'[Survey Name]),FILTER('Survey Response','Survey Response'[Relationship NPS Distribution]="Promoters"))/NumOfRespon-CALCULATE(COUNT('Survey Response'[Survey Name]),FILTER('Survey Response','Survey Response'[Relationship NPS Distribution]="Detractors"))/NumOfRespon) *100

RETURN IF(ISBLANK(thebombdiggedty),0,thebombdiggedty)