Latest Enterprise DNA Initiatives

List of Customers - Buying Patterns

Hi Team,
Request your help regarding customer buying pattern. I am attaching an Excel Data model (with PowerPivot) where I have sales data, product master and criteria table. Basically I would like to find out list of customers who have bought “ABC” product but not “DEF” product. I need to put my slicer from “Criteria” table so that I can share actionable insights with my sales team. I have gone thorough many other sources and videos but I couldn’t find the solution.

Please help me in this regards. You may change or content in any table.

Thanks & Regards
Harish Rathore
Customer Churn.xlsx (224.9 KB)

@harishrathore,

Interesting problem. Here’s how I addressed it:

  1. Created two disconnected tables composed the unique brand names and associated slicers with each.
  2. Standard harvest measure for the first slicer provided Brand Bought, while the second provided Brand Not Bought.
  3. Created the following measure, which creates two virtual table variables - the first captures companies who bought the brand harvested from the first slicer, the second captures companies who bought the brand harvested from the second slicer, and then filters the company list down to those companies that appear in the first virtual table and don’t appear in the second.
  4. Output the results to a scalar that looks like a table, using CONCATENATEX and UNICHAR(10) (the code for hard return)
  5. Used a one-cell table with title for the visual, which provides better formatting control than a card.

Here’s the measure that does the heavy lifting:

 Bought and Not Bought = 
VAR _Bought1 =
    CALCULATETABLE(
        VALUES( Customers[Company] ),
        FILTER( ALL( Data ), Data[Brand Name] = [Harvest Bought] )
    )
VAR _Bought2 =
    CALCULATETABLE(
        VALUES( Customers[Company] ),
        FILTER( ALL( Data ), Data[Brand Name] = [Harvest Not Bought] )
    )
VAR _FirstNotSecond =
    CALCULATETABLE(
        VALUES( 'Customers'[Company] ),
        FILTER(
            'Customers',
            'Customers'[Company]
                IN _Bought1
                && NOT ( Customers[Company] IN _Bought2 )
        )
    )
VAR _Result =
    CONCATENATEX(
        _FirstNotSecond,
        Customers[Company],
        UNICHAR( 10 ),
        Customers[Company], ASC
    )
RETURN
    _Result

And here’s what it looks like all put together:

image

I hope this is helpful. Full solution file provided below.

1 Like

Thanks a lot @BrianJ for your prompt response as always. I have one issue though. In my practical data (my organization’s data) I have long list of brands where my key key filter is “Segment”. When I am putting “Segment” as slicer then nothing is happening in “Brand Name Bought” and “Brand Name Not Bought”. Where as it should also get filtered while selecting segment in slicer. Please find attached screen shot.


Following is my product master;

Brand Name Company Segment
ABC My Company Deluxe Shampoo
DEF Company2 Deluxe Shampoo
GHI Company3 Deluxe Shampoo
JKL Company4 Deluxe Shampoo
MNO My Company Premium Shampoo
PQR Company2 Premium Shampoo
STU Company3 Premium Shampoo
XYZ Company4 Premium Shampoo

Any suggestion on this please.
Thanks & Regards
Harish Rathore

@harishrathore ,

The reason nothing happens when you pull Segment in as a slicer is that the other slicers come from disconnected tables, therefore there’s no relationship/syncing that occurs between your segment slicer and the disconnected brand slicers. To make that work, you need to expand the disconnected tables to include segment, which can be done easily by changing VALUES to ALL in the DAX expressions that create the disconnected tables:

Disconn Bought = ALL( Data[Brand Name], Data[Segment] )

Now, you can create a first level disconnected Segment slicer that dynamically filters the disconnected brand slicer.

Here it is in action:

image

I hope this gets you what you need. Revised solution file attached below.

– Brian
eDNA Forum - Bought and Not Bought Solution2.pbix (40.0 KB)

1 Like

Thanks a lot @BrianJ . Perfect solution. Kudos…

Regards
Harish Rathore

1 Like

Hi @BrianJ . I am finding one difficulty though, while everything is working fine but after selection of slicers, Volume figure is not correct in the table for selected slicer. Pls find attached screenshot and help me in this regard. Volume should be selected segment and selected brand (for those who have bought products)

@harishrathore ,

Can you please do me a favor? I am swamped for the next couple of days with a number of short deadline projects, but don’t want to lose track of this one. Can you please open a new thread with your question and provide a mockup of the specific visual you want to see with correct numbers, so I can validate my solution against that?

Thanks!

– Brian