Weighted Average

[Beatriz]

Hello!

I have two tables, and I need to be able make a column in one do  a weighted average  with values in another. I know how to do the normal average, using the following formula:

AVERAGE(
SELECT(Atualizações[Quantos dias para vencer],
AND(
([Produto] = [_THISROW].[Produto]), 
([Loja] = [_THISROW].[Loja])
)
)
)

 But the problem is that need to do the weight average instead of the normal average, using the quantity of each product as weights, so basically  it needs to be like (example): (quantity1 * missing day1 +  quantity2 * missing day2)/ (quantity1 * quantity2) - using the informations of each row in the table that matches the sotre and product (the selected condition in the formula above)
I thought I could do a loop similar to a form, but I also couldn't do it and didn't find any material online

Does anybody knows how to solve it, please?

[Joseph_Seddik]

Hello Beatriz and welcome to the community!

I'm not sure that's the correct way to calculate a weighted average; why do you multiply quantities together? Averages do not have squared units. 

In any case, please share screenshots of your tables so that we can better help. Thank you.

[tranau151]

I haven't found a good way to do weighted averages in Appsheet as well.  The best I could come up with was to use the INDEX function to achieve the weighted average calculation (i.e SUM(Value * Weight) / SUM(Weight).  I basically sum the results of multiplying the list of values of indexes to the list of weights of indexes and then dividing by the sum of the weight but have to limit the number of line items that I'm calculating.  It looks something like this out to 5 items:

SUM(LIST(
INDEX(SELECT([Value])),1) * INDEX(SELECT([Weight])),1),
INDEX(SELECT([Value])),2) * INDEX(SELECT([Weight])),2),
INDEX(SELECT([Value])),3) * INDEX(SELECT([Weight])),3),
INDEX(SELECT([Value])),4) * INDEX(SELECT([Weight])),4),
INDEX(SELECT([Value])),5) * INDEX(SELECT([Weight])),5)))
/
SUM(LIST(
INDEX(SELECT([Weight])),1),
INDEX(SELECT([Weight])),2),
INDEX(SELECT([Weight])),3),
INDEX(SELECT([Weight])),4),
INDEX(SELECT([Weight])),5)))

If anyone has a better way, please share.  Thanks.