Weighted Average

[Anonymous]

Hi

I'm trying to calculate weighted average for some data I have.

Below is sample of the data. 

Product	Month	Stock	Sold	Stock/Sold
Prod 1	JAN	5000	4800	1.041667
Prod 2	JAN	10	1	10
Prod 3	JAN	20000	19500	1.025641
Prod 4	JAN	5	1	5
Prod 1	FEB	6000	5500	1.090909
Prod 2	FEB	6	3	2
Prod 3	FEB	35000	32000	1.09375
Prod 4	FEB	10	2	5

I wish to calculate the weighted average for the column "Stock/Sold". The probelm is if I do calcuation of average based on this column, the two entries for Prod 2 and Prod 4 will make the average unrealistic cause they have only few items.

DAX will make the avarage = Sum(Stock/Sold)/8 = 3.28

While this is not reflective of the real situation because of volumes of each product.

So, can someone advise me how to do this? TIA

[v-yalanwu-msft]

Hi, @Anonymous ;

Can you tell me what is the formula for calculating 1.06? I don't quite understand the logic.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

I am not sure of what you want.  Try these measures:

St = sum(Data[Stock])

So = sum(Data[Sold])

WA = divide([St],[So])

Hope this helps.

[v-yalanwu-msft]

Hi, @Anonymous ;

So what's your logic? If you want the output result to be only 3.28, then you can use the following formula.

Averge = AVERAGE('Table'[Stock/Sold])

the final show:

If the above one can't help you get the desired result, please provide your expected result with backend logic and special examples.  Thank you.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-yalanwu-msft 

If you see the data above. Prod 2 has 10 in stock and 1 in sold. The ratio sold/stock is 10.

So, if I want to find the average of all ratios, Prod 2 will make the average unrealistic because it's for only 10 items. While other products has thousands of items.

So, if i'm to take average of Stock/Sold it would be 3.28 but the actual average (weighted average) should be close to 1.06.