Forum Discussion

sebastian_ebg's avatar
sebastian_ebg
Helper I
9 years ago

Multi-table DAX Attribution Model

I'm trying to generate an attribution model for our company's emails based off a few different related tables. I'm fairly new to DAX, so I've been fumbling around a bit searching for examples of how ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi sebastian_ebg,

 

Based on research on your sample file, I can't get the cost of specify mailing item and mailiing key. There is no direct relationship between custom to item or item to price.

 

Regards,

Xiaoxin Sheng

sebastian_ebg's avatar
sebastian_ebg
Helper I
9 years ago

I'm not sure if I'm following. There definitely is a relationship between all those fields. The relationship flows as follows:

MailingDataFull[CustomerKey] Many:1 CustomerDetails[CustomerKey]
CustomerDetails[CustomerKey] 1:Many LineItems[CustomerKey]
LineItems[SKU] Many:1 ProductDetails[SKU]

The price is determined from the LineItems[Value] field. You're right in that there's no direct relationship between MailingDataFull and LineItems, however there is a unique relationship between the CustomerKeys in MailingDataFull and the CustomerKeys in LineItems.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi sebastian_ebg,

     

    >>You're right in that there's no direct relationship between MailingDataFull and LineItems, however there is a unique relationship between the CustomerKeys in MailingDataFull and the CustomerKeys in LineItems.

     

    Based on those relationships, I can only get total items and the total spend value, but I can't analysis the detail cost of each mail items. (Your goal is get the detail spend list of each customer per pack)

     

    Regards,
    Xiaoxin Sheng

    • sebastian_ebg's avatar
      sebastian_ebg
      Helper I
      9 years ago

      Ohh, I see the confusion. That's not exactly what I want. What I want, is the total amount that the customer spends, divided equally between each mailing.

       

      That way, there doesn't need to be a direct relationship between the MailingDataFull and the LineItems. As an example (using the above data):

       

      Customer 2 spends $79 total. He opens emails 11 and 12. He has opened two emails total.

      This means that, for this indidivual:
      Email 11 is worth $79/2 = $39.5
      Email 12 is worth $79/2 = $39.5

       

      Another example,
      Customer 8 spends $215.95 total. She opens emails 11, 12 and 14. She has opened three emails total.

      This means that, for this individual:
      Email 11 is worth $215.95/3 = $71.98
      Email 12 is worth $215.95/3 = $71.98
      Email 14 is worth $215.95/3 = $71.98

       

      Hopefully this clarifies it.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi sebastian_ebg,

         

        I create a new calculate table to deal with your requirement, you can refer to below steps:

         

        Add a measure to lineitem table to calculate the total spend per customer.

         

        Total Spend = SUMX(FILTER(ALL(LineItems),[CustomerKey]=MAX([CustomerKey])&&RELATED(ProductDetails[AdvertisedFlag])=TRUE()),[Value])

         

         

        Calculate table:

         

        ResultTable = SELECTCOLUMNS(FILTER(MailingDataFull,[OpenCount]>0),"CustomKey",[CustomerKey],"MailingKey",[MailingKey],"Total",[Total Spend])

         

        Add calculate column to calculate the average spend.

         

        Average = [Total]/COUNTROWS(FILTER(ResultTable,[CustomKey]=EARLIER(ResultTable[CustomKey])))

         

        Result table:

         

         

        Visual:

         

         

        Regards,

        Xiaoxin Sheng