Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |