Forum Discussion
Tricky Situation with SUM
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 |
4 Replies
- wdx223_DanielCommunity Champion
- bipowerbixHelper I
Hi Daniel, The "+0" at the end gives the result for Kiwi. Is there any documentation for the "+0" online?
- wdx223_DanielCommunity Champion
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/