Exclude null values while calculating NPS score

Anonymous — Mon, 27 Mar 2023 14:02:53 GMT

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),

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

Re: Exclude null values while calculating NPS score

wdx223_Daniel — Tue, 28 Mar 2023 04:04:59 GMT

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),

score

)

topic Re: Exclude null values while calculating NPS score in DAX Commands and Tips

Exclude null values while calculating NPS score

Re: Exclude null values while calculating NPS score