# Revenue calculation at different aggregation level based on filters, total incorrect

Hello, I am trying to calculate revenue at engagement level. There could be only one engagement, but multiple opportunities associated with it.

engagement_id opp_id eng_status opp_status eng_revenue opp_revenue
1 10 Won launched 5000 \$1,000
1 11 Won closed lost 5000 \$2,000
1 12 Won prospect 5000 \$1,000
2 21 Won closed lost \$1,000
2 22 Won launched \$2,000
3 31 Lost closed lost \$3,000 0

Here are the conditions: `eng_status` must be Won, otherwise attribute 0. If an engagement has `eng_revenue`, use that for the entire engagement. If `eng_revenue` is missing, sum up all `opp_revenue` for `opp_stage` = âlaunchedâ (and other filters, but lets use only one for the sake of example).

Based on these conditions, for engagement_id
1/ `eng_status` is Won, and `eng_revenue` is available == use `eng_revenue` (5000) as calculated revenue
2/ `eng_status` is Won ; no `eng_revenue` availale, sum `opp_revenue` in launched `opp_status` == only 1 launched `opp_id` 22, use 2000 as calculated revenue
3/ `eng_status` is not Win. attribute 0

And then when I am showing the Total, this should be 5000+2000 = 7000, without duplicating per opp_id. like below:

engagement_id sumOver()_1 sumOver()_2 final revenue opp_id
1 5000 5000 10
1 5000 5000 11
1 5000 5000 12
2 2000 2000 21
2 2000 2000 22
3 0 31
Total 5000 2000 7000

So far, I have created 2 fields `sumOver()_1` and `sumOver()_2` by engagement_id and 1 field to add these two to give me final revenue number . The problem is for first `sumOver()`, `eng_revenue` is getting counted for every opportunity and Total shows 15,000 instead of 5000. And the `final_revenue` column only totals the `sumOver()_1` field, so 15,000 instead of 7000. Any suggestions?

Formulae:
sumOver()_1 =

``````sumOver(
sumIf({eng_revenue},
{eng_status}='Won'
AND isNotNull({eng_revenue})
)
, [{engagement_id}]
, POST_AGG_FILTER
)
``````

sumOver()_2 =

``````sumOver(
sumIf({opp_revenue},
{eng_status}='Won'
AND isNull({eng_revenue})
AND isNotNull({opp_revenue})
AND {opp_status}='Launched'
)
, [{engagement_id}]
, POST_AGG_FILTER
)
``````

final_revenue = `sumOver()_1 + sumOver()_2`

Can you make a rank field that ranks based on the opp_status where launcdhed=1 , closed lost =2, etcâŚ and have it grouped by engagement_id. Then filter to rank of 1.

rank_field = rank([ifelse({opp_status}=âlaunchedâ,1,{opp_status}=âclosed lostâ,2, etcâŚ) ASC],[engagement_id])

1 Like

Hi Max, thank you for your comment. I have 6 other fields than opp_status I need to filter on, would have to create rank for everything. Also, I would like to avoid using rank in sum revenue if possible - reason being I tried this on my other dashboard, and whenever I add new columns, there is a chance the rows get duplicated, so I ended up with multiple rows having rank=1 and having lots of dupes in the total revenue. Is there any other way to accomplish this?

You will filter on all of the fields. A filter works across the whole visual. I donât think you would need to create a rank unless you need to do more logic besides what you are talking about above.

Adding fields wonât âduplicateâ rows. It just shows you those groups.

Another option is that you could divide by the count of rows per engagment id.

countOver(engagement_id,[engagement_id])

This would get you 15,000 / 3 on your final_revenue and then if you sum it it will come to 15000 on your engagement of one.

1 Like