Sum measure with Null or blank value

[Anonymous]

Hi,

I have a measure that sets the profit target (in %) for each project. However some projects are not entitled to a target and therefore I'd like to set-up "N/A", or "blank", or "null", what ever works. Note that 0 is a target (no profit basically) so 0 cannot be used instead of blanks.

This is the measure

Profit target = SWITCH(LEFT(SELECTEDVALUE(dProjects[Project No.]),5),"T4035",0.25,"T4042",0.0,"T4045",0.0,"T4048",0.0,"T4051",0.25,"T4053",-0.25,"T4056",0.25,"null value or something").

Then I have another measure that will make a calulation based on "profit target".

Income target =

SUMX (

dProjects,

('dProjects'[Profit target] + 1 ) * 'fForecastUSD'[Spendings]

)

The problem is that when I enter anything else than a number at the end of the switch function in the first measure (here represented by "null value or something") "income target" returns an error. 

How do i get the "income target" to ignore non numeric values?

thanks for your help.   

[v-yalanwu-msft]

Hi, @Anonymous ;

You can fix total with the ISINSCOPE function. If not, can you share a simple file and the output you want?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Anonymous ;

You could modify it.

Income target =
IF([Profit target]<>"null value or something",
SUMX (
dProjects,
('dProjects'[Profit target] + 1 ) * 'fForecastUSD'[Spendings]
))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I have tryed, but i actually have to update all the measures and formulas that use the profit target as an argument with an IF, which is pretty inconvinient, and my totals are not calculated anymore. 

[Anonymous]

Thanks, i'll try that

[johnt75]

You can either use BLANK() as the final argument to SWITCH or don't have any final argument, as the default behaviour of SWITCH is to return a blank value if none of the conditions are met.

[Anonymous · Accepted solution]

Thanks johnt75. I made a table of the returned values of the swtich, and BLANK() is actually returned as "0". Any idea?

[johnt75]

can you post the code?

[Anonymous]

my bad discard the previous messages, it actually return blank, and I was looking at a measure i made that calculates the target and sets o if blank. My problem is not fundamentally resolved because my totals do not get calculated, but I found a workaround which is to split between 2 tables, one containing projects with targets, and the other which is containing projects without targets. I 'll close the ticket and reopen one with the new issues i'll face.