Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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)
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)
Thank you Greg_Deckler. This worked.
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
112 | |
96 | |
96 | |
75 | |
72 |