Formula Changes based on Context, incorrect values

[joshs444]

I created a table to show the margin we are making per lb by item number):

When I use a different category than Item, I get an incorrect value:

 I would like the Modifier to average the items in that category and figure out the average "margin per pound" for all items in that category.

My formula for Margin per lb is:

Average Margin Per lb =

(DIVIDE([Average Margin],SUM('Item List'[Weight pe Case]),0)

)

Average Margin formula is:

Average Margin =

var UNIT = DIVIDE([Sum Margin],SUM('Sales by Item'[QTY]))

RETURN

AVERAGEX('Sales by Item', unit)

Any insights or help would be appreciated.

Thanks

[tamerj1]

Hi @joshs444 

What is the formula for [Sum Margin]?

[joshs444]

Sum Margin = [Sum Sales] - [Sum Cost]

Sum Cost = sum('Sales by Item'[Extended Cost])

Sum Sales = sum('Sales by Item'[Extended Price])

Would it work if I was using SUMX?

Thanks!

[tamerj1 · Accepted solution]

Hi @joshs444 

not sure modifier is from which table therefore the most general formula would be 

Average Margin Per lb =
AVERAGEX (
VALUES ( 'Sales by Item'[Item Code] ),
CALCULATE (
DIVIDE (
DIVIDE ( [Sum Margin], SUM ( 'Sales by Item'[QTY] ) ),
SUM ( 'Item List'[Weight pe Case] ),
0
),
CROSSFILTER ( 'Sales by Item'[Item Code], 'Item Listm'[Item Code], BOTH )
)
)