Weighted average with condition

[nbrandborg]

Hi Power BI experts! 

I have a dataset with the last rolling 12 months where I have calculated the Total Weighted Average across all the months I'm looking at. The calculation is quite simple and is working fine.

TotalWeightedAvg = DIVIDE(
    SUMX('Monthly Sales (Product Level)','Monthly Sales (Product Level)'[AdjustedREUnits] * 'Monthly Sales (Product Level)'[YearlyRatio]),
    SUMX('Monthly Sales (Product Level)','Monthly Sales (Product Level)'[AdjustedREUnits]),0)

But I also need one where I get the weighted average per month. How do I add a condition where I look at a column (named MonthRank) where I have ranked the individual months from 1-12, so I get the Weighted Average per Month? There will be multiple rows for each MonthRank.

Thank you in advance! 

Have a nice weekend.

[amitchandak · Accepted solution]

@nbrandborg , This looks fine for Weighted Avg. For simple Avg you need

TotalWeightedAvg = Averagex(Values('Date'[Month Rank),  calculate( DIVIDE(
SUMX('Monthly Sales (Product Level)','Monthly Sales (Product Level)'[AdjustedREUnits] * 'Monthly Sales (Product Level)'[YearlyRatio]),
SUMX('Monthly Sales (Product Level)','Monthly Sales (Product Level)'[AdjustedREUnits]),0))  )

Rolling avg example

Rolling 12 = calculate(AverageX(Values('Date'[MONTH Year]),[MeasurementPct]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))