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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.