How to calculate ratio?

Hello,

I work on sugar and I would like to calculate ratio(like how many leads became opportunity(conversion ratio) or a win/loss ratio for the opportunities).

Mathematicly speaking, I need to make a division.

I tried on regular reporting but the section where you can do average/count/etc. does not offer "divide".

Can it be done in regular reporting?

Otherwise, can it be done in advanced reporting? Will it be necessarty to have SQL query that will calculate the ratio?

Do you have an example? Can't find anything in documentation.

Thanks!

Parents Reply Children
  • One idea for a quick fix to this - using calculated fields for your example of Opportunity win/loss ratio:

    Create a new Integer field in Opportunities, make it a calculated field based on the status of your Won/Lost status using the ifElse() logic. The goal here is to have this integer field = 1 if Won, and = 0 if Lost (and perhaps leave it blank if the opportunity is still ongoing).

    Then you should be able to run a regular summation (or w/ details) report and use the AVG: of your new calculated field.

    Let's say you had 5 Won and 5 Lost Opportunities, the result of this field in a report would be 1, 1, 1, 1, 1, 0, 0, 0, 0, 0 - which would average out to 50%.

    I haven't tested this out, but it seems like it should work. And then the same logic would apply to your Lead conversion ratio field.

    Let us know if you need help with the ifElse() conditions.