Forum Discussion
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?
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) )
