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.
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.