Forum Discussion

joshs444's avatar
joshs444
Frequent Visitor
3 years ago
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 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's avatar
    tamerj1
    3 years ago

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

3 Replies

    • joshs444's avatar
      joshs444
      Frequent Visitor
      3 years ago
      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's avatar
        tamerj1
        Community Champion
        3 years ago

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