Categorise Orders by largest product group - shopify data

[JackEnviro]

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

 

[Zubair_Muhammad · Accepted solution]

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] )

[Zubair_Muhammad · Accepted solution]

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] )

[Zubair_Muhammad · Accepted solution]

@JackEnviro

Please see attached file

[JackEnviro]

Hi Zubair

That's perfect - thank you!

Jack