Weighted Moving Average Measure

[yaman123]

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

[v-eqin-msft]

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]

Hi @v-eqin-msft 

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