Forum Discussion
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 to achieve the result we're looking for.
The attribution model we're looking at using is linear attribution because it works best with the way our industry operates.
The logic behind this is as follows:
[Attributed Individual Mailing Sales] = [Total converted customer purchase value] / [Total customer opens]
If I were to plot a simple example, where the X axis is the individual mailings, the Y axis is the individual customers, and the values are the total customer conversion divided by the number of emails opened, it would look something like this:
| CustomerKey | 10 | 11 | 12 | 13 | 14 | Conversion |
| 1 | $24.99 | $24.99 | $49.97 | |||
| 2 | $39.50 | $39.50 | $79.00 | |||
| 3 | $- | |||||
| 4 | ||||||
| 5 | $74.00 | $74.00 | $148.00 | |||
| 6 | $23.00 | $23.00 | $46.00 | |||
| 7 | $- | |||||
| 8 | $71.98 | $71.98 | $71.98 | $215.95 | ||
| 9 | $- | |||||
| 10 | $3.80 | $3.80 | $3.80 | $3.80 | $3.80 | $19.00 |
| Attribution | $3.80 | $115.28 | $138.28 | $102.79 | $197.77 | $557.92 |
The data model that I have created is in an example file hosted here.
I've tried creating a few measures and calculated columns to determine the total conversion value in the LineItems table which is properly filtering to only customers who have a direct conversion and have opened emails. The measure is returning the correct value ($557.92), while still properly obeying filtering.
CALCULATE(SUM(LineItems[Value]),LineItems[MailOpens] > 0, LineItems[Direct Attribution] = TRUE())
The problem I'm having is that I now need to be able to apply that on a per-mailing basis, while still obeying the product filtering. This is where I'm falling apart, as the calculation isn't returning the attribution values (the final total row) in the table above, which was manually calculated.
Can anyone point me in the right direction for the correct calculation using this data model? Or perhaps even a way to re-work the model that will make it work in PBI/DAX as I have access to my own ETL tools?
13 Replies
Hi sebastian_ebg,
Can you share us a sample file to test? It is hard to know your table relationships from your screenshot.
Regards,
Xiaoxin Sheng
Unfortunately it's very difficult for me to share a sample file as it contains sensitive data. I may spend a day anonymising and reducing the data so that I can share it because it still has me stumped.
In the mean time, this is the relationships of the tables:
LineItems[SKU]* - ProductDetails[SKU]1 LineItems[CustomerKey]* - CustomerDetails[CustomerKey]1 MailingDataFull[CustomerKey]* - CustomerDetails[CustomerKey]1
Hopefully this helps. If not, I will work on anonymising the data.
I've updated the post to clarify the problem and added an example with simplified data.
I've also uploaded the example file here to hopefully get some help with it.
Regards,
Sebastian
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
