Forum Discussion

yaman123's avatar
yaman123
Post Partisan
5 years ago

Weighted Moving Average Measure

Hi, 

 

I am looking to create a new measure which will give me the weighted moving average YTD 

 

I have a sample date column which is used in a slicer to select a period (this is the last day of the month) e.g 28/02/2021 

 

I have a weighted avg bfat measure and a ytd coll litres measure already created.

 

I am looking to create a weighted avg bfat ytd measure and to replicate the excel formula in power bi : 

=SUMPRODUCT(Weighted Avg Bfat YTD,Coll Litres YTD)/SUM(Coll Litres YTD)

 

TIA

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi yaman123 ,

     

    According to my understanding, you want to calculate Weighted Average in DAX, right?

     

    I believe you can get more hints from these links:

    Weighed Average DAX Expression

    DAX - Weighted Averages: Another Use of SUMX() | P3 Adaptive

    Weighted Average in Power BI - Goodly

     

    If these do not make sense, please provide some data samples and the expected output.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • yaman123's avatar
      yaman123
      Post Partisan
      5 years ago

      Hi Anonymous 

       

      I need to calculate the weghted moving average YTD. 

       

      I have a date slicer - sample date which is selected 

       

      I have a measure which is giving me the weighted avg bfat - 

       

      Weighted Avg Butterfat =
      VAR __CATEGORY_VALUES = VALUES('FM_MONTHLY_QTY_PBI'[BUTTERFAT_AVERAGE])
      RETURN
          DIVIDE(
              SUMX(
                  KEEPFILTERS(__CATEGORY_VALUES),
                  CALCULATE(
                      AVERAGE('FM_MONTHLY_QTY_PBI'[BUTTERFAT_AVERAGE])
                          * SUM('FM_MONTHLY_QTY_PBI'[COLLECTION_LITRES])
                  )
              ),
              SUMX(
                  KEEPFILTERS(__CATEGORY_VALUES),
                  CALCULATE(SUM('FM_MONTHLY_QTY_PBI'[COLLECTION_LITRES]))
              )
          )
       
      I have the below sample data
       
      so if i select sample date 28/02/2021, the weighted moving average result should be 4.23 for 28/02/2021