How can I prevent double-counting revenue in my analysis?

Question & Answer
, ,
1

How can I prevent double-counting revenue in my analysis when:

My data shows the opportunities as rows. Each opportunity may show multiple times if they have different:

  •     Products associated

  •     Partners associated

  •     Partner types

    Current situation: I tried using

sum(avg({Opportunity revenue}, [{Opportunity ID}]))

This doesn’t work when grouping by partner type because each opportunity ID becomes unique within its partner type. This leads to double-counting in the overall numbers.

What calculated formulation should I use? Please be specific vs. sharing the paper about possible solutions. No tech background. Thank you.

2

Hello @llan, what I would normally do in a situation like this, is create some kind of denseRank function that would rank each time a value was repeated. This then allows you to create a filter for that visual, where you require it to only return values where the rank is equal to 1. That will ensure you avoid aggregating repeated values.

Here is some documentation on denseRank.

It seems like ranking the partner type could possibly be the best way to avoid multiplying the aggregation, so it would look something like this:
denseRank([{Partner type} ASC], [{Opportunity ID}], PRE_AGG)

Now, if you needed to account for multiple fields, you could try concatenating them into a single field, ranking that by opportunity ID, and then filtering.

Repeat Value Definition = concat({Product}, {Partners}, {Partner type})

denseRank([{Repeat Value Definition} ASC], [{Opportunity ID}], PRE_AGG)

That is only necessary if all 3 of the values you mentioned above cause the revenue to be duplicated. Let me know if you have any further questions!

3

Thanks. I noticed my pipeline data is based on Product level revenue. Here’s how it works:

  • Same Opportunity ID with Different Products:

If an opportunity has two different products, it shows twice with different revenue for each product
This is CORRECT - keep both entries

  • Same Opportunity ID with Different Partners:

If an opportunity has two different partners, it shows twice with the same product
This is a DUPLICATE - keep only one entry

When Summing Total Revenue:

KEEP all entries when same Opportunity ID has different products
REMOVE duplicates when same Opportunity ID and same product show up with different partners

Please see the image below where revenue highlighted in red should be removed as duplicates.

If so, what calculated field you are suggesting to add? Thank you.

Sample
Sample1216×748 33.2 KB

1 Like
4

Hello @llan, okay, I understand now. We can reconfigure the denseRank function I wrote above to resolve this:
denseRank([{Partner Name} ASC], [{Opp ID}, {Product Name}], PRE_AGG)

Also, if Category is a field in your dataset, it may be worth adding that into the partition as well:
denseRank([{Partner Name} ASC], [{Category}, {Opp ID}, {Product Name}], PRE_AGG)

Then, you can just add this calculated field as a filter to the visual. Select custom filter from the dropdown, input 1 into the text field, and make sure exclude nulls is selected in the last dropdown. That should resolve the issue you are facing. Let me know if you have any remaining questions!

5

This is perfect! Thank you!