Trying to use AVERAGEIFS to create a weighting based on Month and Item Type

[Anonymous]

I am trying to create a weight for product attributes using: Price / Average Price of the Same Item Type in the Same Month. I have done the same calulations in excel using an AVERAGEIFS function in the denominator but cant seem to replicate the same results in Power BI.

My current column formula is: 

Weight = 'Sales'[Price] / CALCULATE(AVERAGE('Sales'[Price]), FILTER ( 'Sales', 'Sales'[Month-Year] = 'Sales'[Month-Year] && 'Sales'[Item Type] = 'Sales'[Item Type] ))

but this doesnt seem to be calculating the expected results.

Power BI Results

Item #	Item Type	Month-Year	Price	Weight
8001	1	January 2019	2150	0.2973
8002	2	January 2019	10304	1.4248
8003	1	January 2019	7811	1.0801
8004	1	January 2019	6540	0.9043
8005	2	January 2019	15007	2.0751
8006	1	January 2019	4991	0.6901
8007	1	January 2019	5989	0.8281
8008	1	February 2019	9028	1.2484
8009	2	February 2019	2784	0.3850
8010	2	February 2019	4744	0.6560
8011	1	February 2019	1078	0.1491
8012	2	February 2019	13386	1.8510
8013	1	February 2019	9877	1.3658
8014	2	March 2019	3764	0.5205
8015	2	March 2019	11024	1.5244

Excel Results

A	B	C	D	E
Item #	Item Type	Month-Year	Price	Weight
8001	1	Jan-19	2150	0.3912
8002	2	Jan-19	10304	0.8142
8003	1	Jan-19	7811	1.4212
8004	1	Jan-19	6540	1.1899
8005	2	Jan-19	15007	1.1858
8006	1	Jan-19	4991	0.9081
8007	1	Jan-19	5989	1.0897
8008	1	Feb-19	9028	1.3554
8009	2	Feb-19	2784	0.3993
8010	2	Feb-19	4744	0.6805
8011	1	Feb-19	1078	0.1618
8012	2	Feb-19	13386	1.9201
8013	1	Feb-19	9877	1.4828
8014	2	Mar-19	3764	0.5091
8015	2	Mar-19	11024	1.4909

where the weight function is: = D2 / AVERAGEIFS($D:$D,$B:$B,B2,$C:$C,C2)

Any help would be greatly appreciated, thanks!

[HotChilli · Accepted solution]

Please test this as I looked at relatively briefly.

The calculated column should be:

ChilliWeight = 'Sales'[Price] / CALCULATE(AVERAGE('Sales'[Price]), 
                                    FILTER ( 'Sales', 
                                             'Sales'[Month-Year] = EARLIER('Sales'[Month-Year]) && 
                                             'Sales'[Item Type] = EARLIER('Sales'[Item Type] )))

The reason the original code didn't work is that the denominator was 7231.8 for every row

[Anonymous]

This seems to have worked! Thank you very much!