Issue with an average based on a specific filter

Copper Contributor

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 IDPRODUCTQNTPRICE
1apple210
1pear211
2apple110
2plum213
3raspberry315
3strawberry116
4apple110
4raspberry215

 

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:

PRODUCTAverage order
apple39,33
pear..
plum..
raspberry..
strawberry.
strawberry.

 

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

3 Replies

Hi @Imrageth 

 

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

 

Sample.png

 

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

@Lorenzo works like a charm. thanks so much!

@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