Tricky Situation with SUM

[bipowerbix]

Here is the Data I am using:

Type               Spent   Returns 

Apple	100
Orange	50	200
Banana	25	150
Pear		500
Kiwi

If I use 

ROI = sum(Data[Returns]) / sum(Data[Spent])

I get the following

Information on Data:

the spent and returned data is filled in by the user and will remain blank until then. So will have to create DAX measures that will assign 0 where there is a blank. However, if we do that, we get Infinity and NaN for Pear or Apple. So we have to HardCode such incidents to 0.

Questions

 1)  I also want to show the Kiwi line item as well and assign 0 to the ROI column for Kiwi. 

 2) How to deal with Infinity, show 0 instead of Infinity.

3) Result in either 1 of the shown expected output will work

Any suggestions, tips, and tricks are welcome.

I want to get the following output along with totals at the end for the first two columns and the average for the third column, shown below:

Expected Output

Type   Spent   Returns  ROI

Apple	100
Orange	50	200	4
Banana	25	150	6
Pear		500
Kiwi
Total	175	850	5

OR

Type   Spent   Returns  ROI

Apple	100		0
Orange	50	200	4
Banana	25	150	6
Pear		500	0
Kiwi			0
Total	175	850	5

[wdx223_Daniel · Accepted solution]

[bipowerbix]

Hi Daniel, The "+0" at the end gives the result for Kiwi. Is there any documentation for the "+0" online?

[wdx223_Daniel]

i just learned this from somewhere in internet. but you can read this article.

https://www.sqlbi.com/articles/how-to-return-0-instead-of-blank-in-dax/

[bipowerbix]

This works for this case, I noticed that this doesn't work if other fields such as Dynamic Date are joined to the table. I have attached my post here.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Combinations-not-working-as-intended/m-p/...