Filter customers who are interested in a specific product category

[Anonymous]

I have the following tables:

Customers_Lookup which contains customerID, name, address ...etc.

Orders_Lookup which contains OrderID, CustomerID, TotalReceipt ...etc.

Product_Lookup which contains ProductID, ProductName, Category ...etc. 

Orders_Data which contains OrderID, CustomerID, ProductID, Quantity, Price ...etc.

I need to get a list of customers who never bought a specific product. However, they bought other products that are under that specific product category. For example which customers are interested in Accessories but never bought a Nike Bracelet.

Here's what I've done:

Specific Product Category Quantity = CALCULATE([Total Quantity], Products_Lookup[ProductGroup] = "Accessories")

Specific Product Quantity = CALCULATE([Total Quantity] + 0 , Products_Lookup[ProductNumber] = 113000423)

Then I've created a visual with Customer, Specific Product Category Quantity, and Specific Product Quantity.

Then I've created a visual filter where (Specific Product Category Quantity > 0) and (Specific Product Quantity = 0).

I think this solution isn't flexible as the variables are hardcoded.

Do you have any other recommendations?

[Anonymous]

I need to get a list of customers who never bought a specific product. However, they bought other products that are under that specific product category. For example which customers are interested in Accessories but never bought a Nike Bracelet.

Here's what I've done:

Specific Product Category Quantity = CALCULATE([Total Quantity], Products_Lookup[ProductGroup] = "Accessories")

Specific Product Quantity = CALCULATE([Total Quantity] + 0 , Products_Lookup[ProductNumber] = 113000423)

Then I've created a visual with Customer, Specific Product Category Quantity, and Specific Product Quantity.

Then I've created a visual filter where (Specific Product Category Quantity > 0) and (Specific Product Quantity = 0).

I think this solution isn't flexible as the variables are hardcoded.

Do you have any other recommendations?

[kriscoupe · Accepted solution]

 Hi @Anonymous,

You are nearly there. You can adapt your formulas to get this working and remove the hardcoding. Try

Specific Product Category Quantity = 
CALCULATE(
    [Total Quantity],
    ALL( Products_Lookup )
    VALUES( Products_Lookup[ProductGroup] )
)

Specific Product Quantity = [Total Quantity]

Then pop a slicer on the page for Products_Lookup[ProductName]. When you select a product in that slicer the Specific Product Quantity measure will equal sales for that product. The Category Quantity measure will remove any filters from the Product_Lookup table (the ALL) and the VALUES will put filters back on at the ProductGroup.

You also don't have to have the measures in the matrix either. You can still use the visual level filters on it.

Hope it helps

Kris

[Anonymous]

Thanks a lot, @kriscoupe 

That totally solved the problem.

Really appreciate it.

Best wishes,

Muhammad Ezzat