Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Weighted Average

Hi

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

Below is sample of the data. 

 

ProductMonthStockSoldStock/Sold
Prod 1JAN500048001.041667
Prod 2JAN10110
Prod 3JAN20000195001.025641
Prod 4JAN515
Prod 1FEB600055001.090909
Prod 2FEB632
Prod 3FEB35000320001.09375
Prod 4FEB1025

 

 

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

 

 

 

 

Need Help

4 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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.

       

       

       

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    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.