NPS calculation at Company level

[aggarwal25]

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

[Greg_Deckler · Accepted solution]

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)

[aggarwal25]

Thank you Greg_Deckler. This worked.