[erindpepi Contributions: 0Level 1☆]

Hi guys!

I am trying to create a saved search to help our finance department (Revenue per class) but I get weird numbers when I add calculation of Contribution Margin Percentage/ratio.

Please see the attached screenshot for what I have in my results.

The formula I have to calculate the Contribution Margin Ration is the following:

NVL((case when {accounttype} = 'Intäkt' then {signedamount} else null end),0) – NVL((case when {accounttype} IN ('Kostnad' , 'Kostnad för sålda varor') then {signedamount} else null end),0)

As I have summary type SUM, the formula is adding upp to percentage for every row it finds thus returning wrong information. So if division of values in two different rows is 100% per row, the summary would return 200% which is not true. (Please see screenshot for this, last column to the right).

I even tried replacing case when with decode but the result is the same. Whenever I add SUM function in the formula I get 'An unexpected error has occured'. This happens only when I add SUM to the DIVISOR. I could understand it would be a division by zero problem but I have added a NULLIF function:

(SUM(DECODE({accounttype},'Intäkt', NVL({signedamount},0),0)) – SUM(DECODE({accounttype}, 'Kostnad för sålda varor', NVL({signedamount},0),0)))

/

SUM(DECODE({accounttype}, 'Intäkt', NULLIF({signedamount},0)))

Would really appreciate an answer so that we do not have to do the calculations in Excel.

Thanks in advance!

Erind
This is a cached copy. Click here to see the original post.

[MChammaTX Contributions: 0Level 1☆]

Not 100% sure exactly how to solve but I can offer some things to try.

1-Try including the sum itself in the formula and change to Summary type of Max. (if this works make sure to audit results carefully)

2- Try wrapping the denominator in a null if to avoid divide by zero issues

[erindpepi Contributions: 0Level 1☆]

Originally posted by MChammaTX

View Post

Not 100% sure exactly how to solve but I can offer some things to try.

1-Try including the sum itself in the formula and change to Summary type of Max. (if this works make sure to audit results carefully)

2- Try wrapping the denominator in a null if to avoid divide by zero issues

Thank you so much! I already had a NULLIF for division with zero but the first alternative you suggested made the trick. The Summary Type MAX was all I was missing.

Again thank you!!

[Author]

Posts