Matching customer email marketing data to sales data

[APM]

Hello,

I need help with finding the appropriate formula:

I have 3 tables:

1. A list of customers (column G below) 
2. A list of promotional SKUs (Column I below) 
3. A sales report with the SKU and the name of the customer that purchased it (M:P below)

Here's a consolidated picture of what my tables have:

I need to find out what % of customers in that list bought one of the promotional SKUs.

Does anyone have an idea of how I would do this?

[v-yetao1-msft]

Hi @APM 

I still don't quite understand your question.

In your sales table, all customers are in the customer table. Similarly, the skus in the sales table are also in the SKU table. What percentage do you want now ? Does each customer purchase a percentage of the total purchase quantity ? Can you provide your calculation rules and the results you expect (in the form of data) ?

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[richbenmintz]

Hi @APM ,

I think essentially what you are looking to do is count your customers and then divide by the customers that purchased a promo sku. I created a sample pbix based on your screen cap, much easier if you inclue the data in a table(s), I slightly modifed to ensure that one of the purchases was not a promo. The model has three measures, see below

Customer Count = COUNTROWS(customers)

Promo Cust = CALCULATE(COUNTROWS(VALUES(trans[customer])), FILTER('trans', 'trans'[sku] in VALUES('promo_skus')))

% Promo Cust = DIVIDE([Promo Cust], [Customer Count])

results in the following.

attached you will find the sample pbix.

hope this helps

[APM]

So this is regarding email marketing, and ultimately I want to see of the list of customers that got an email (the list of names), how many of them bought (sales report) one of the items promoted in the email (promotional skus list).

[ryan_mayu]

@APM 

what's the expected result? could you explain the calculation logic?