Calculate CSAT, NPS, & CES where not every response has a score associated with it

[DataFun]

I am a new Power BI user.  I am trying to calculate CSAT, NPS, and CES in a single report page.  The challenge I have is not every survey response has a score for each of these items.  Where no score is given, the cell has the text "No Reply".  Specifically for NPS, I create a new column in Power BI that is type Whole Nunmber.  In this column, I assing a value of -100 to any NPS response of 1-6.  For 7-8, I assign a value of 0, and for 9-10 I assign a value of 100.  The problem I have, is the formula results in an error because it can't handle the text from the column with the survey response score.  I have tried using Power Query Editor to change the "No Reply" cells to 9999.  Back in Power BI, I can calculate the NPS that ignores the cells with a 9999 value by creating a page filter.  The page filter, however, affects other visualizations on the page.  I tried setting a filter just on the visualization, but I am unable to edit the filter. 

Is there a different/better way I could go about ignoring the cells that have the "No Reply" data in them?  I don;t want to remove thoise rows in my data as they may have other data related to CSAT or CES.  

Thank you.

[DataFun · Accepted solution]

I think I figured it out.  In my input data, if I transform the No Reply and set the value to null, I can set these values in my calculated column to Blank().  This appears to work.

[Fowmy]

@DataFun 

Gald it worked for you,

[Fowmy]

@DataFun 

Can you share some sample data with the desired output to have a clear understanding of your question?
Mention whether you want a calculated column or measure.
You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.

[DataFun]

Here is an example of the data for NPS.  Since the rating/score given needs to be converted, I am using a calculated column.

NPS Score (In my Original Data Set)	NPS Calc (Calculated Column)
1	-100
10	100
9	100
No Reply	9999
8	0
5	-100
No Reply	9999
9	100
9	100
9	100
3	-100
10	100

The reason I chose 9999 for those with No Reply is I received an error of mismatched type, so I changed these to a number thinking I could filter them out.  For my NPS calculation I would consider the 10 other responses.  What I would expect as a result is:

Average (-100, 100, 100, 0, -100, 100, 100, 100, -100, 100) = 30 for NPS.

As mentioned, in Power BI I can set a page filter that correctly calculates this, but it also affects other visualizations on the page.  I tried to set a filter only on the visualization, but Power BI wouldn't allow me to set a filter at that level.