Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
YannLG
Frequent Visitor

Sum measure with Null or blank value

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.   
1 ACCEPTED SOLUTION

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

View solution in original post

8 REPLIES 8
v-yalanwu-msft
Community Support
Community Support

Hi, @YannLG ;

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
Community Support
Community Support

Hi, @YannLG ;

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:

vyalanwumsft_1-1649737608745.png


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.

 

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. 

Thanks, i'll try that

johnt75
Super User
Super User

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.

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

can you post the code?

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors