cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Filter customers who are interested in a specific product category

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?

1 ACCEPTED SOLUTION
Resolver IV
Resolver IV

 Hi @Muhammad-Ezzat,

 

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

View solution in original post

2 REPLIES 2
Resolver IV
Resolver IV

 Hi @Muhammad-Ezzat,

 

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

View solution in original post

Thanks a lot, @kriscoupe 

That totally solved the problem.

 

Really appreciate it.

 

Best wishes,

Muhammad Ezzat

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors