Powerpivot Weighted Average Measure

Grinch356

New Member
Joined
Jan 6, 2015
Messages
31
Hello,

I have a data that calculates forecast accuracy. I have this as a measure - the measure formula is:

Mapemeasure:=IF(DIVIDE(ABS(SUM(Accuracy[DEMAND_QTY])-SUM(Accuracy[ORIGINAL_SO99FRC])),SUM(Accuracy[DEMAND_QTY]),0)<1,1-DIVIDE(ABS(SUM(Accuracy[DEMAND_QTY])-SUM(Accuracy[ORIGINAL_SO99FRC])),SUM(Accuracy[DEMAND_QTY]),0),0)

Now, in my data, each product belongs to a product family, vendor and sales area and each product has been ascribed a grade of A, B ,C OR D.

When I pivot up my data I want to be able to see my accuracy in such a way:

At grade level it should simply take the average 'MAPEMEASURE' of each sku and give that as a total, so total Accuracy for grade A is an avearge of the individual acuuracy numbers of Grade A products.

At vendor level and family level however, I need to apply weighting since A products count 50% towards total, B products 30% and C 20%.

With this in mind, I thought I just needed the following accuracy measure to give me these weightings in my model but it does not work - when I view at grade level my results are not straight averages.

Accuracy Measure:=SUMX(DISTINCT(Accuracy[Grade]),(Accuracy[Mapemeasure])*SWITCH(Accuracy[Grade], "A", .5 , "B", .3 , "C", .2))

Can anyone help?
 

Forum statistics

Threads
1,085,495
Messages
5,384,007
Members
401,871
Latest member
allemandi

Some videos you may like

This Week's Hot Topics

Top