Exclude null values while calculating NPS score

[Anonymous]

Hi, I am using this dax code to calculate the NPS 

NPS Score demo =

//be sure you have a question in your survey asking "How likely would you be to recommend us?" with a scale of 0-10

VAR detractors = CALCULATE(

           COUNTROWS('nps'),

                     FILTER('nps',

                     'nps'[NPS]<=6)

           )

//don't forget to make your NPS question column into a whole number data type

VAR passives = CALCULATE(

           COUNTROWS('nps'),

                     FILTER('nps',

                     'nps'[NPS]=7)

           )

           +CALCULATE(

                     COUNTROWS('nps'),

                      FILTER('nps',

                     'nps'[NPS]=8)

           )

VAR promoters = CALCULATE(

           COUNTROWS('nps'),

                     FILTER('nps',

                     'nps'[NPS]>=9)

           )

VAR total = COUNTROWS('nps')

VAR score = (promoters/total*100)-(detractors/total*100)

RETURN If(

                     ISBLANK(score),

                     0,

                     score

           )

It works perfectly fine, but the issue is I have null values in the column I am using to calculate the NPS. This is what my data looks like 

Client	NPS	Session	Attendees
Client 1	7	Skills tomorrow	34
Client 1	4	Skills tomorrow	23
Client 1	6	CV writing	645
Client 1	9	CV writing	67
Client 2	10	Digital Marketing
Client 2	10	Digital Marketing
Client 2	7	Audience developemnt
Client 2	10	Audience developemnt
Client 3	9	Marketing techniques
Client 3	3	Marketing techniques
Client 3	2	Marketing techniques
Client 3		Marketing techniques	45
Client 3		Marketing techniques	34

[wdx223_Daniel · Accepted solution]

NPS Score demo =

//be sure you have a question in your survey asking "How likely would you be to recommend us?" with a scale of 0-10

VAR _tbl= FILTER('nps',NOT(ISBLANK('nps'[NPS])))

VAR detractors = COUNTROWS(FILTER(_tbl,'nps'[NPS]<=6))

//don't forget to make your NPS question column into a whole number data type

VAR passives =COUNTROWS(FILTER(_tbl,'nps'[NPS]=7||'nps'[NPS]=8))

VAR promoters =COUNTROWS(FILTER(_tbl,'nps'[NPS]>=9))

VAR total = COUNTROWS(_tbl)

VAR score = (promoters/total*100)-(detractors/total*100)

RETURN If(

                     ISBLANK(score),

                     0,

                     score

           )