# Issue with an average based on a specific filter

Copper Contributor

# Issue with an average based on a specific filter

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?

3 Replies

# Re: Issue with an average based on a specific filter

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

# Re: Issue with an average based on a specific filter

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

# Re: Issue with an average based on a specific filter

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