Customers who have NOT bought a product

[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.

[bkwohls]

Has anyone found the solution to the Multiple Products question posed above?

[v-ljerr-msft · Accepted solution]

Hi @gng,

If I understand you correctly, you should be able to use the formula below to create a new measure, then show it on a Table visual with Sales[Customer ID] column on the report to get your expected result.

Measure =
IF (
    HASONEVALUE ( Items[Item] ),
    CALCULATE (
        SUM ( Sales[Sales] ),
        FILTER (
            Sales,
            NOT ( CONTAINS ( Sales, Sales[Item], VALUES ( Items[Item] ) ) )
        )
    ),
    SUM ( Sales[Sales] )
)

Note: make sure there is no any relationship between the Item table and Sales table.

Here is the sample pbix file for your reference. 

Regards

[simone000000000]

Hi,

Very nice solution. How can adapt it if i want to see customers who did not buy more than one specific product?

Because with this solution, when i select more than one product, It doesn't work 

[Asantos2020]

Hello @v-ljerr-msft ,

How would I include a date slicer, which when selected, customers who didin't buy the selected product within that period showed up in the list?

Thank you!

[gng]

Hi @v-ljerr-msft,

With your help, I am able to get Sales Amount for a customer who didnt order a specific product. Is there a way to calculate Sales for specific product(s) for a customer who didnt order another specific product?

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

Thanks for your time and help!

[gng]

Hi @v-ljerr-msft 

This solution works great. I really appreciate your help and time.

Regards