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

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors