Customers who have NOT bought a specific product but have bought other specific product(s)

[gng]

Hello! I am trying to build this report where I have to identify customers who have NOT bought a particular product but has bought other products.

I have attached an example spreadsheet which Customer, Item and Sales tables. Objective is to dynamically select an item from a slicer and the resulting table should give Customers who have NOT bought that item with a Total Sales Amount (from other products) in descending order.

In this example, show only those customers who bought either "x" or "w" but not "v".

[v-jiascu-msft]

Hi @gng,

It seems that you already get it in your another post. All customers in the table visual have bought some products. The measure removes those customers who bought a specific product. So the table visual shows the customers who didn't buy a specific product but buy other product(s). 

Best Regards!

Dale

[gng]

Hi @v-jiascu-msft,

I got the solution for if I choose a specific product then the table filters the data for all the customers who have not bought that product and shows sales amount for all other products for these customers.

Now what I am looking for is if I choose a specific product then the table should filter the data for all the customers who have not bought that product and shows sales amount for NOT 'all' but just '1 or 2 other products' for these customers.

So basically in the example, if product 'v' is chosen then the table should show only those customers who have bought either 'x' or 'w' or both with the corresponding sales amount instead of sales amount for all of the other products.

Hope this clarifies the problem.

Thanks,

G

[seun112]

"I got the solution for if I choose a specific product then the table filters the data for all the customers who have not bought that product and shows sales amount for all other products for these customers." 

Hi , How did you go about this please ?

[gng · Accepted solution]

I found the solution myself:

Measure = IF ( HASONEVALUE ( UnrelatedUniqueProductsTable [item] ), CALCULATE ( SUM ( order[Sales] ), FILTER ( order, AND(OR(order[item_id] = "36", order[item_id] = "56"), NOT ( CONTAINS ( order, order[item], VALUES ( UnrelatedUniqueProductsTable [item] ) ) )) ) ), SUM ( order[Sales] ) )

Thanks

G