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

Question & Answer
,
#1

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

#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
#3

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?

#4

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