Division zero constraint

[linoyel]

Hi,

In chart dimension calculation, I have a division, but we know that it's impossible to divide  into zero. Here's my calculation:

(Sum(INCOME) / (Sum(COST)+ Sum(SALARY))) - 1 

The result is percent.

If (Sum(COST)+ Sum(SALARY) = 0, then the result should be 100%.

How to write it? Pls help!

[linoyel]

If there is no investment (Cost of investment = 0), how would I present ROI? It's actually an infinite number... It's impossible, right?

[linoyel]

Exactly! But it's not good to show "-" in the report... It's just not right... because then I do SUM(ROI) and I get less then it's supposed to be. Guess I'll just ask business what they want me to do in this case.

[#4]

It all depends on what you mean by saying "impossible". It's impossible in real life, but can be in your data source by some kind of reason (error, rounding, etc). So YOU must decide, how to display the result of such occasion. A real big value, a text like "error!" or "infinity" or just to substitute a little fixed value to costs (like 0.000001) - every way is possible using if(...) solution posted above.

[linoyel]

Thank you! That's the right answer to the original question

Though after posting it, I understood that it's supposed to be smth else then a 100% (basically, I'm thinking how to present infinity lol)

[linoyel]

I agree. It's not a mistake, it happens in real life. I'll think further how to present it Thanks for thinking with me

[#7]

I don't know your task, but SUM(ROI) is (logically) quite a strange operaion... Only if costs (investments) are equal for all the operations. Imagine: 1) inv=1mil, sales=0, 2) inv=1, sales=2. SUM(ROI) = 0, but I would kill such investment manager 😉

[linoyel]

The business wanted to see ROI, but I have data when on certain days investment is 0 but there are sales, so ROI is "-". Sum(ROI) doesn't really make sense, as well as ROI calculation by itself. I'm getting to a conclusion that ROI might not be a right value to present in my case... already told the business about it...