Return the biggest product category (by sales) for the first order for each unique customer

[ErikOmni]

Hi!

I'm trying to analyse the behavior of customers based on what the bought in their first order. I want to tag each customer based on the biggest product category (by sales) of their first purchase. This would allow me to easily analyse their behavior. Honestly, I'm quite lost even how to start attacking this problem and haven't been able to track down a solution in the forum.

Data consists of order data with rows for each item in the order. Below you see in a example of how the data looks like and the yellow column is the end result I'm looking for.

In above example one customer has made two orders (first order containing three items, second order containing two items). The first order this customer ever placed was 2022-08-31. The biggest category in terms of revenue in the first order was Bedset (710 is bigger than 510). So I then want this customer to be tagged as "Bedsets".

Note: Data stretches from 2020-01-01 to 2022-08-31 and plenty of customers have first order date before 2020-01-01 so those customer could just be left as blank in the column.

Thanks in advance! 

[Greg_Deckler]

@ErikOmni Try:

Column = 
    VAR __Cust = [CustomerID]
    VAR __First = [First order date]
    VAR __Table = FILTER('Table7',[CustomerID] = __Cust && [First order date] = __First)
    VAR __Max = MAXX(__Table,[Revenue])
    VAR __Category = MAXX(FILTER(__Table,[Revenue] = __Max),[Category])
RETURN
    __Category

[ErikOmni]

Hi! First of all, thank you so much for answering @Greg_Deckler . You have helped me out multiple times!

The formula you suggested didn't fully do the trick. This is an example of the outcome I got:

In this case I have 1 customer that has made 5 orders. First order was made on 7 January 2021 and the biggest category in that order was Bedsets. However, the formula seems to take the biggest category for all orders that this customer has made.

When I implemented your formula it looked like this. It's a bit weird that it didn't work since it is filtered on first order date. 

Any ideas?

[Greg_Deckler · Accepted solution]

@ErikOmni I used the wrong column for filtering:

Column = 
    VAR __Cust = [CustomerID]
    VAR __First = [First order date]
    VAR __Table = FILTER('Table7',[CustomerID] = __Cust && [Date] = __First)
    VAR __Max = MAXX(__Table,[Revenue])
    VAR __Category = MAXX(FILTER(__Table,[Revenue] = __Max),[Category])
RETURN
    __Category

[ErikOmni]

@Greg_Deckler 

Thank you! this is the third time you have helped me out. I've learned some new things from every post. 

[Greg_Deckler]

@ErikOmni Happy to help! 🙂