Exclude NULL values from VAR Measures

Question

Hi folks,&nbsp;I have a series of visuals that calculate a Net Promoter Score style output.&nbsp;&nbsp;I was given elsewhere in this forum the below measure, you will note that at the bottom that NULL values are assigned a score of 0.&nbsp;&nbsp;This is skewing the results, and I am wondering how I exclude NULL values from the calculation?&nbsp;Any help would be greatly appreciated,ThanksJamie&nbsp;&nbsp;NPS Score = //be sure you have a question in your survey asking "How likely would you be to recommend us?" with a scale of 0-10VAR detractors = CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;COUNTROWS('Repository'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILTER('Repository',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Repository'[NPS]&lt;=6)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)//don't forget to make your NPS question column into a whole number data typeVAR passives = CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;COUNTROWS('Repository'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILTER('Repository',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Repository'[NPS]=7)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;+CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;COUNTROWS('Repository'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('Repository',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Repository'[NPS]=8)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)VAR promoters = CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;COUNTROWS('Repository'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILTER('Repository',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'Repository'[NPS]&gt;=9)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)VAR total = COUNTROWS('Repository')VAR score = (promoters/total*100)-(detractors/total*100)RETURN If(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ISBLANK(score),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;score&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)

anonymous · Accepted Answer

Hi&nbsp;jamesclark458&nbsp;,
Please try to filter the table that you want to exclude null value, then use this new table to calculate, like below dax formula, please refer:
var tmp= filter(All("Table Name"),[ColumnName]&lt;&gt;"null")
&nbsp;
Best regards,Community Support Team_ Binbin YuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Exclude NULL values from VAR Measures

2 Replies

Related content

Top 10 excluding value

RANKX excluding a value

Measure returning incorrect value

Exclude Certain Value from Measure

Exclude value from measure