topic Tricky Situation with SUM in DAX Commands and Tips

Tricky Situation with SUM

bipowerbix — Mon, 27 Mar 2023 23:10:39 GMT

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

Type Spent Returns ROI

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

Re: Tricky Situation with SUM

wdx223_Daniel — Tue, 28 Mar 2023 01:27:24 GMT

Re: Tricky Situation with SUM

bipowerbix — Tue, 28 Mar 2023 04:46:25 GMT

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

Re: Tricky Situation with SUM

wdx223_Daniel — Tue, 28 Mar 2023 05:59:52 GMT

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/

Re: Tricky Situation with SUM

bipowerbix — Tue, 28 Mar 2023 23:56:04 GMT

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/3159186#M113405