Customers who didn't buy a particular product

[rit372002]

Hello,

I am looking for prospective customers who bought certain products but didn’t buy a particular product and want to get a list of those customers.

For e.g.: In the attached pbix, I am looking for customers who didn’t buy “fungicide” this year. Out of these 6 customers, 4 of them (408, 697, 1343 and 2332) are those prospects. They didn’t buy it last year either but I am interested in this years sale.

2837 and 4441 bought those so I don’t want to see them in list.

https://drive.google.com/file/d/1s_XViotCTArr7_kfpXnU5frIyqaemOyJ/view?usp=sharing

If I use slicer based on product, then I see customers with those products, I am looking for opposite of that.

Thanks in advance ~

-RK

[BrianJ]

@rit372002,

Check out this thread - I think it’s basically the same use case as yours.

Select what IS NOT in filter

Hi! In the below chart I have a Product Category filter. I select “Auto Loan”. This selection shows the count of various data elements. In this case the count of homeowner or renter as well as the count of categories of net worth. I want a table showing details of items NOT in the product category selection. How do I create a table that displays what is NOT selected in the “Product Category” selection? [Capture]

I hope this is helpful.

• Brian

[rit372002]

Thanks Brian. I am halfway through but not there yet.

I watched the video. It removes the product from the calculation but still keeps the client who bought it.

In this situation, 2 rows of Fungicide is gone which belonged to client 2837 and 4441) but I need the client themselves are gone.

I used this formula:

Exclude? = IF ( MAX ( 'PRODUCT'[PRODUCT] ) IN ALLSELECTED ( producttest[PRODUCT] ), "Y", "N" )

How can I get it to the client level?

Thanks for your help.
-rk

[BrianJ]

@rit372002,

Got it – thanks for the clarification. Working on a solution now – should have it for you shortly.

• Brian

[AntrikshSharma]

@rit372002 Try this:

Antriksh =
VAR _CROPYEAR =
    CALCULATE ( MAX ( 'CPP AND CN POS'[Crop Year] ), ALL ( 'CPP AND CN POS' ) )
VAR BoughtFungicide =
    CALCULATETABLE (
        DISTINCT ( 'CPP AND CN POS'[Grower Master Key] ),
        'CPP AND CN POS'[Crop Year] = 2020,
        'PRODUCT'[PRODUCT] = "FUNGICIDE"
    )
VAR AllCustomerIn2020 =
    CALCULATETABLE (
        DISTINCT ( 'CPP AND CN POS'[Grower Master Key] ),
        'CPP AND CN POS'[Crop Year] = 2020
    )
VAR NonFungicide =
    EXCEPT ( AllCustomerIn2020, BoughtFungicide )
VAR Result =
    CALCULATE (
        SUM ( 'CPP AND CN POS'[Sales Amount] ),
        'CPP AND CN POS'[Crop Year] = _CROPYEAR,
        NonFungicide
    )
RETURN
    Result

[rit372002]

@AntrikshSharma @BrianJ

This is pretty brilliant solution! If I just put this measure in filter pan as being not null, I get what I am looking for. I will have to just create different measures for different product prospects, which is exactly how it should be.

Thank you so much both of you to get me to the finish line!

-rk

[BrianJ]

@rit372002,

As always, @AntrikshSharma’s solution is right on point. I took a quite different approach. I’ve still got one small bug in it, that at 3 AM is proving difficult to resolve, but I will look at it with fresh eyes tomorrow and once I get it working will just post it for comparison sake/learning value,

Glad you got what you need though - thanks, @AntrikshSharma.

• Brian

[AntrikshSharma]

@BrianJ Wow, you really should sleep now!

[rit372002]

@BrianJ and @AntrikshSharma

Sorry, There is an update on this. Client wants to have that as columns instead of measure.

Because: if the user wants a list of customers with the prospect info, they would have to do multiple exports and merge all those files together into one to collect that information in once place. that’s not ideal.

the best solution is to include those flags in one export file. Fungicide prospect (Yes/No), adjuvant prospect… etc. each of those flags would be in the export file,

So basically, I should have 3 columns : Fungicide Prospects, Adjuvant Prospects and Max-In prospects which should be “Y” or “N” and summarize at the Customer Key level.

If you bought everything except Fungicide then you are fungicide prospect…so on and so forth.

Any help is appreciated.

-rk

[rit372002]

I attempted to create a column and I tried this.

It gives me this:

So all blanks are where they didn’t buy fungicide. If I try to add one more column with same formula and just change it to “Adjuvant”, I get circular dependency error.

I think I am close but not sure how do I get around this.

Thanks in advance,
-RK