Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Exclude null values while calculating NPS score

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 
 
ClientNPSSessionAttendees
Client 17Skills tomorrow34
Client 14Skills tomorrow23
Client 16CV writing645
Client 19CV writing67
Client 210Digital Marketing 
Client 210Digital Marketing 
Client 27Audience developemnt 
Client 210Audience developemnt 
Client 39Marketing techniques 
Client 33Marketing techniques 
Client 32Marketing techniques 
Client 3 Marketing techniques45
Client 3 Marketing techniques34
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago
    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
               )

1 Reply

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago
    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
               )