Issue with an average based on a specific filter

[Imrageth]

Hi,

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.

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

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.

It would be great if we could present it like this:

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

Now, how to put it in a formula or pivot?

[#2]

Hi @Imrageth 

With data formated as Table (not mandatory but recommended) named TableOrder:

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

[#3]

@L z. works like a charm. thanks so much!

[#4]

@Imrageth 

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