Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joshs444
Frequent Visitor

Formula Changes based on Context, incorrect values

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

 

joshs444_2-1674939708355.png

 

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

joshs444_3-1674939788487.png

 

 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

 

 

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

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @joshs444 

What is the formula for [Sum Margin]?

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors