1 Reply Latest reply on Apr 20, 2013 3:40 AM by Andrew Watson

# Sum of revenue with a many-to-many object relationship

Hello, I have a problem with many to many joins and summing a distinct revenue value. For example, in one connection I have two tables:

1. Opportunity table with OpportunityID, OpportunityRevenue columns

2. Attribute table with OpportunityID, AttributeID, AttributeCategoryID, AttributeTypeID columns

The Join between the tables is LEFT JOIN [Opportunity].[OpportunityID] = [Attribute].[OpportunityID]

This is essentially a many-many relationship between Opportunity and Attribute, with Attribute having a hierarchy of Type > Category > Attribute.

When I use a COUNTD(OpportunityID) I get a nice distinct count of Opportunities however I slice on any level of the Attribute hierarchy. This is great, however my problem is I want to do a SUM of OpportunityRevenue by distinct OpportunityID. For example, consider a \$100K Opportunity that has the following related records in the Attribute table:

OpportunityID | AttributeID | AttributeCategoryID | AttributeTypeID

123 | 1 | 1 | 1

123 | 2 | 1 | 1

123 | 3 | 1 | 1

In plain English, the Opportunity has Attributes 1,2,3 attached to it.

If I put SUM(OpportunityRevenue) into Measure Values, and AttributeTypeID = 1 into Columns, I will see revenue of \$300K due to the LEFT JOIN from Opportunity. The table it is building behind the scenes looks like this:

OpportunityID | AttributeID | AttributeCategoryID | AttributeTypeID | OpportunityRevenue

123 | 1 | 1 | 1 | \$100K

123 | 2 | 1 | 1 | \$100K

123 | 3 | 1 | 1 | \$100K

In understand mathematically how this happens, but the business logic is incorrect. This is a very common and simple problem to solve in OLAP (Many-Many dimensions) that seems maddeningly impossible in Tableau. Any ideas? Thanks

• ###### 1. Re: Sum of revenue with a many-to-many object relationship

This is a common problem but there is a way around it using an advanced formula. Try something like:

`IF FIRST()==0 THEN WINDOW_SUM(SUM([REVENUE]),0,IIF(FIRST()==0,LAST(),0)) END`

I think you need to partition this by OpportunityID to make it work - in other words put OppId on the Level Of Detail shelf and set the Compute Using of the advanced table calcs.

If you're unfamiliar with table calcs you'll probably find this challenging to get right - this link contains a comprehensive guide to table calcs: http://drawingwithnumbers.artisart.org/want-to-learn-table-calculations/

Good luck

Andrew