Forum Discussion
Solved
Formula Changes based on Context, incorrect values
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 av...
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 )
)
)
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!
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 )
)
)