Forum Discussion
Multi-table DAX Attribution Model
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.
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