5 Replies Latest reply on Oct 30, 2014 10:13 AM by KK Molugu

# how can I show -100% margin?

I have some data in which revenue is \$0, cost is a positive or negative value, and OI is likewise a positive or negative value.  The calculation of OI margin is OI/Rev, which is mathematically problematic.  However, logically it should show 100% or -100%, based on OI, since the cost reduces the margin overall.  Is there a way to include the 100% and -100% in the calculation for margin?  It seems like the aggregate margin does not correctly reflect the transactional margins.

• ###### 1. Re: how can I show -100% margin?

Karen:

For the community to better help you, please attach the data to the question and what do you mean by 100% or -100% based on OI.

..kk

• ###### 2. Re: how can I show -100% margin?

Hi-

Operating Income (OI) = Revenue - Cost

OI Margin = OI/Revenue

Where there is Cost but \$0 Revenue, the Margin is not calculated at 100%, but it seems this removes significant activity from the transactional and aggregated Margin.  Is there a way to change the #DIV/0! to 100% in the case of rows 3 and 4, and -100% in the case of row 5 in the sample below?

• ###### 3. Re: how can I show -100% margin?

Karen:

Hope this helps and what you are looking for. You need a calculation to see if the Rev is 0 then make margin 1 else use our formula.

..kk

• ###### 4. Re: how can I show -100% margin?

Looks good!  It needs to distinguish between good impact and bad impact, though.  Where OI is negative, the Margin should be -100%, but where OI is positive, Margin should be +100%.  Is it possible to use something like an AND clause to specify if Rev = 0 AND OI >0 then 1, else if Rev = 0 AND OI<0 then -1, else sum(OI)/Sum(rev)?

• ###### 5. Re: how can I show -100% margin?

That should work and in that case first 2 records will have the OI margins are they are, 3 and 4 will be 100%, and the last one will be -100%.

Is that what you want

..kk