Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ErikOmni
Helper I
Helper I

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

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.

 

ErikOmni_0-1663434718825.png

 

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! 

 

 

 

 

1 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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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:

 

ErikOmni_2-1663524095783.png

 

 

 

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. 

 

ErikOmni_3-1663524321639.png

 

Any ideas?

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler 

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

@ErikOmni Happy to help! 🙂


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.