Forum Discussion
Multi-table DAX Attribution Model
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.
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
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.5Another 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.98Hopefully this clarifies it.
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
Thanks so much for all your help Xiaoxin! This is so close to working now.
The only part that's not working is that the [Conversion Value] measure isn't obeying other filtering when applied to the result table.
Conversion Value = CALCULATE(SUM(LineItems[Value]), CustomerDetails[MailOpens] > 0, Attribution[DirectAttribution] = TRUE())
Example: If you bring the [Conversion Value] measure in as a card, and filter by "Primary" for the ProductDetails[ProductType], it properly shows $557.92. However, once applied to the ResultTable calculation, it shows the total of $1655.64, even when filtering by Primay product type.
If you change the [ConversionValue] measure to the following, it works fine - but I want to be able to apply the filter manually.
Conversion Value = CALCULATE(SUM(LineItems[Value]), CustomerDetails[MailOpens] > 0, Attribution[DirectAttribution] = TRUE(), ProductDetails[ProductType] = "Primary")
Is there any way to get it to obey filtering at the measure level, so that the measure updates based on filters without needing to hard code it?
Regards,
Sebastian