topic Re: Formula Changes based on Context, incorrect values in DAX Commands and Tips

Formula Changes based on Context, incorrect values

joshs444 — Sat, 28 Jan 2023 21:05:40 GMT

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

Re: Formula Changes based on Context, incorrect values

tamerj1 — Sun, 29 Jan 2023 18:53:48 GMT

Hi joshs444

What is the formula for [Sum Margin]?

Re: Formula Changes based on Context, incorrect values

joshs444 — Mon, 30 Jan 2023 13:13:53 GMT

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!

Re: Formula Changes based on Context, incorrect values

tamerj1 — Mon, 30 Jan 2023 14:15:39 GMT

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 )
)
)