cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JackEnviro Regular Visitor
Regular Visitor

Categorise Orders by largest product group - shopify data

Hello

 

I have a relationship table as below. Where the Shopify Order table contains a list of all transactions at individual Sku code level. I would like to add a calculated column (I think!) that categorises each transaction by the highest value product category sale in that order. Thi aim is that each order can be attributed to a Product group for marketing ROI analysis.

 

For example: 

The first 6 transactions in the table are "Order 1", therefore look-up the product catgeory of each sku (i.e. different types of beers, beer wines, coffees) in the "StockValues" table "Group1" which define the meta cetegory, then return the product category with the highest value. So that say an order made up of lots of different drink types (e.g. Beer, Wine, Juice, Water, Coffee etc) the highest selling drink category by value is identified as "Beer" and this order can be identified as predominantly a beer order rather than a "Coffee" order.  

 

Thanks

Jack

 

 Tables.JPG

 

Relationship table.JPG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Categorise Orders by largest product group - shopify data

hI @JackEnviro

 

tRY THIS COLUMN

 

Column =
VAR T1 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Shopify Orders' ),
            'Shopify Orders'[Order] = EARLIER ( 'Shopify Orders'[Order] )
        ),
        "Group", RELATED ( StockValues[Group1] ),
        "Value", [Lineitem Price] * [Lineitem count]
    )
VAR T2 =
    SUMMARIZE ( T1, [Group], "Values", SUMX ( T1, [Value] ) )
VAR T3 =
    TOPN ( 1, T2, [Values], DESC )
RETURN
    MINX ( T3, [Group] )
Try my new Power BI game Cross the River

View solution in original post

Super User
Super User

Re: Categorise Orders by largest product group - shopify data

@JackEnviro

 

Please see attached file

 

caegorize.png

Try my new Power BI game Cross the River

View solution in original post

3 REPLIES 3
Super User
Super User

Re: Categorise Orders by largest product group - shopify data

hI @JackEnviro

 

tRY THIS COLUMN

 

Column =
VAR T1 =
    SELECTCOLUMNS (
        FILTER (
            ALL ( 'Shopify Orders' ),
            'Shopify Orders'[Order] = EARLIER ( 'Shopify Orders'[Order] )
        ),
        "Group", RELATED ( StockValues[Group1] ),
        "Value", [Lineitem Price] * [Lineitem count]
    )
VAR T2 =
    SUMMARIZE ( T1, [Group], "Values", SUMX ( T1, [Value] ) )
VAR T3 =
    TOPN ( 1, T2, [Values], DESC )
RETURN
    MINX ( T3, [Group] )
Try my new Power BI game Cross the River

View solution in original post

Super User
Super User

Re: Categorise Orders by largest product group - shopify data

@JackEnviro

 

Please see attached file

 

caegorize.png

Try my new Power BI game Cross the River

View solution in original post

JackEnviro Regular Visitor
Regular Visitor

Re: Categorise Orders by largest product group - shopify data

Hi Zubair

 

That's perfect - thank you!

 

Jack

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,556)