Convert calculated columns to a measure: Customers who have purchased X, have also purchased..

[Anonymous]

Hi, Excel user here who's gradually learning PowerBI & DAX.

I'm trying to build a BI function, were you can select a specific product in our product line, and then be shown the total sales for all customers who has purchased this product.

With my Excel background, I've solved this using calculated columns, and hardcoded the product ID number into the code, which works, but doesn't offer the functionality that I know a measure would provide.

So far, what I've done is:

1. Created a calculated Column with:

Product Number = SWITCH(Sales[ProductID];
                                    "DAT-5435";Sales[CustomerKey];"")

Which returns the customer ID, if the line has the product "DAT-5435" or blank, with SWITCH enabling me to fill in as many products as I would like.

2, my second column:

Lookup = if(
    ISBLANK(    
        LOOKUPVALUE(
            Sales[Product Number];Sales[Product Number];Sales[CustomerKey]));"No";"Yes")

Thus looking up the Customer ID from my first calculated column in all lines, and returning a "Yes" / "No" value, if there is a match, so all customers who have purchased the product historically has a "Yes" value, which I can use as a filter.

As far as I can understand, to build this dynamically - so I don't have to hardtype the product ID's - I need to create a copy of our product table that doesn't have a relationship to any of the other tables, to use as my 'input variable'.

Then i need to create a measure that uses some combination of if / calculate / countrows and probably sumx.

Anyone here who can help me convert these calculated columns into a functioning measure?

Thanks in advance.

[OwenAuger]

Hi @Anonymous

I just posted a reply to this thread which has somehow disappeared, so apologies if another post turns up!

Welcome to Power BI 🙂

What you are wanting to do is a type of 'Basket Analysis' - see here (Basket Analysis on DAX Patterns)

With Basket Analysis you are typically wanting to analyse Orders that contain, say, both Product A & Product B.

In your case your 'basket' is actually your Customer, as you want to see sales of all products to Customers that have purchased a particular Product (hopefully I've interpreted that correctly).

Here is a dummy model that illustrates how it can be done: Dropbox link

The key elements are:

1. Create a secondary lookup table for Product called 'Filter Product', with an inactive relationship to your Sales table.
2. Create a measure that returns Sales of all Products to Customers who have purchased Products selected from 'Filter Product' table.

In my dummy model, the tables look like this (I didn't bother with a Product lookup table):

 And I have defined a couple of measures that are variants of the pattern on DAX Patterns:

Sales Amount for Customers Who Purchased Selected Product Within Current Filter Context = 
CALCULATE (
    [Sales Amount],
    CALCULATETABLE (
        SUMMARIZE ( Sales, Sales[CustomerKey] ),
        ALL ( Sales[ProductID] ),
        USERELATIONSHIP ( Sales[ProductID], 'Filter Product'[Filter ProductID] )
    )
)

Sales Amount for Customers Who Purchased Selected Product Ever = 
CALCULATE (
    [Sales Amount],
    CALCULATETABLE (
        SUMMARIZE ( Sales, Sales[CustomerKey] ),
        ALL ( Sales ),
        USERELATIONSHIP ( Sales[ProductID], 'Filter Product'[Filter ProductID] )
    )
)

1. The first measure returns sales to Customers who purchased Products selected from 'Filter Product' table within the current filter context (except for Product).
2. The second measure returns sales to Customers who purchased Products selected from 'Filter Product' table ever.

This is the sample output in my dummy model:

• The first measure includes sales to Cust-Jones for all Products in Month 1 only, since Cust-Jones purchased Prod-004 in Month 1.
• The second measure includes sales to Cust-Jones for all Products in Months 1 & 2, since Cust-Jones purchased Prod-004 at least once in the entire Sales table.

Anyway, hopefully that gives you some ideas to play with for your model.

Regards,

Owen 🙂

[Anonymous]

Hi Owen, 

Messed around with it yesterday and couldn't get it to work, just noticed this morning that I forgot about the "inactive" relationship - had it active before.

And now it works! Thanks!

Also as follow up question, should this work if the user selects multiple items?

Further, I was wondering if it would be possible to expand the analysis further and make it a little more complicated.. 🙂 

What would you need to do, if you want to be shown total sales before and after the first date a customer has purchased the product selected in the filter?

Thinking you need to search the date coloum for the minimum value where the purchase if the item is made, and then kinda use that as a "stamp" that is unique for every single customer with such purchase, then use that stamp for every single line, and then create to measure, where the first measure only summarizes sales if the salesdate is greater than the "stamp" date, and then another measure, where it only sum's if the date is smaller than the "stamp" date?

I.e. : After purchase date:  if(date>stamp;sum(sales);"")

and: Before purchase date: if(date <stamp;sum(sales);"")

Just not sure how to get the stamp going..

[OwenAuger · Accepted solution]

Hi @Anonymous

Welcome to Power BI 🙂

What you are wanting to do is a sort of 'basket analysis' (see Basket Analysis on DAX Patterns).

In your case the 'basket' is the Customer rather than the Order.

I have uploaded a simple dummy model that hopefully you can adapt to your needs:

Dropbox link

The dummy model is similar to the one discussed on the DAX Patterns page, with a 'Filter Product' table which has an inactive relationship to the Sales table. (I didn't bother creating a Product lookup table.)

Then I defined two measures:

Sales Amount for Customers Who Purchased Selected Product Within Current Filter Context = 
CALCULATE (
    [Sales Amount],
    CALCULATETABLE (
        SUMMARIZE ( Sales, Sales[CustomerKey] ),
        ALL ( Sales[ProductID] ),
        USERELATIONSHIP ( Sales[ProductID], 'Filter Product'[Filter ProductID] )
    )
)

Sales Amount for Customers Who Purchased Selected Product Ever = 
CALCULATE (
    [Sales Amount],
    CALCULATETABLE (
        SUMMARIZE ( Sales, Sales[CustomerKey] ),
        ALL ( Sales ),
        USERELATIONSHIP ( Sales[ProductID], 'Filter Product'[Filter ProductID] )
    )
)

1. The first measure is exactly the pattern from DAX Patterns, and will give you sales for all Customers who bought any Products selected from the 'Filter Product' table, within the current filter context (except for Product).
   With this measure, if Cust-Jones purchased Prod-004 in Month 1 but not Month 2, and you select Filter Product = Prod-004, you will see all Cust-Jones sales in Month 1 but not in Month 2.
2. The second measure is a variation, and returns sales for all Customers who ever bought any Products selected from the 'Filter Product' table.
   With this measure, if Cust-Jones purchased Prod-004 in Month 1 but not Month 2, and you select Filter Product = Prod-004, you will see all Cust-Jones sales in Months 1 & 2.

Here is what the output looks like:

Hopefully this is something like what you were looking for 🙂

Regards,

Owen