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

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

    Zachary Mazzoncini

      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