Issue with an average based on a specific filter

Question

Hi,&nbsp;Below is a simple represantion of the structure I'm dealing with. I want to find out average order value for apple, pears, etc but make sure it include other products in the average.&nbsp;ORDER IDPRODUCTQNTPRICE1apple2101pear2112apple1102plum2133raspberry3153strawberry1164apple1104raspberry215&nbsp;Using this example, we can tell that apple exists in orders 1, 2 and 4 and I would like to sum all products on those orders and take an average of them.Order value for order 1 is 42, order 2 is 36 and order 4 is 40 therefore average order which includes apples is 39,33.&nbsp;It would be great if we could present it like this:PRODUCTAverage orderapple39,33pear..plum..raspberry..strawberry.strawberry.&nbsp;Now, how to put it in a formula or pivot?

lorenzo · Answer

Hi&nbsp;Imrageth&nbsp;&nbsp;With data formated as Table (not mandatory but recommended) named&nbsp;TableOrder:&nbsp;&nbsp;in F2:=LET(
  StackAvg, LAMBDA(seed,product,
    LET(
      orders,  FILTER(TableOrder[ORDER ID], TableOrder[PRODUCT]=product),
      also,    IFNA(XMATCH(TableOrder[ORDER ID],orders),0),
      scope,   FILTER(TableOrder[[QNT]:[PRICE]], also),
      VSTACK(seed, ROUND(SUMPRODUCT(CHOOSECOLS(scope,1), CHOOSECOLS(scope,2)) / COUNT(UNIQUE(orders)),2))
    )
  ),
  Products, SORT(UNIQUE(TableOrder[PRODUCT])),
  Averages, REDUCE("AVERAGE",Products, StackAvg),
  HSTACK(VSTACK("PRODUCT",Products), Averages)
)

imrageth · Answer

Lorenzo&nbsp;works like a charm. thanks so much!

lorenzo · Answer

Imrageth&nbsp;&nbsp;You're welcome. At the bottom of each reply you get here there's a link to Mark as solution... - This helps people who Search - Thanks

Forum Discussion

Issue with an average based on a specific filter

3 Replies

Resources

ORDER ID	PRODUCT	QNT	PRICE
1	apple	2	10
1	pear	2	11
2	apple	1	10
2	plum	2	13
3	raspberry	3	15
3	strawberry	1	16
4	apple	1	10
4	raspberry	2	15

PRODUCT	Average order
apple	39,33
pear	..
plum	..
raspberry	..
strawberry	.
strawberry	.