Basket Analysis

[marsa]

Hi all,

I'm working on a basket report with the following model. I have 3 tables, the first one is a "product" table Product_code and Product_name as columns. The second is a customer table with the columns Customer_code, Customer_name, and Parent_customer_id. Finally, sales table product_code and customer_code columns.

I managed to copy a DAX formula from another post:

PurchaseBothProducts = 
var InitialPurchase = VALUES(Sales[Customer_code])
var CombinePurchase = CALCULATETABLE(VALUES(Sales[Customer_code]), ALL('Product'),
TREATAS(VALUES(Product_filter[Product_code]),Sales[Product_code]))

Return
IF( SELECTEDVALUE('Product'[Product_name]) = SELECTEDVALUE(Product_filter[Product_name]), BLANK(), COUNTROWS(INTERSECT(InitialPurchase,CombinePurchase)))

It works as you might expect, but now I'm running into an issue. The formula just takes into account the customers that have purchase product directly from us, but we have cases when a group of potential customers joints together to make a purchase, during the negotiation and sales everything is under just one customer therefore in the measure it will take it just like 1 customer when in reality it can be 10-15 customers together.

Is there a way to change the DAX measure or the PowerQuery process for it to measure as well as the "Indirect Customer"?

Thanks.

[v-lili6-msft]

hi  @marsa 

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[mahoneypat · Accepted solution]

If I understand your situation correctly, I would propose the following.  Make another table with two columns - one with all your direct customer codes (individual customers and group customer codes), and the other for the indirect customer codes.  Each of your individual customers would have the same value in both columns.  Group customers would also have additional rows listed as one of the group.

You can then make a relationship (or not, if you want to use TREATAS) on the first column to your sales table on Customer code, but do you analysis on the 2nd column (VALUES() of it would include each customer whether they bought individually or as part of a group).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[marsa]

This worked, I created the table and the relationship instead of using TREATAS just for convenience. Thanks for your help and sorry for the late response.