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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors