Trying to calculate per pound metrics for a category

[Anonymous]

I'm sorry if this has been asked before. I searched for similar solutions and tried to adapt them for mine, but wasn't able to get things to work. I've been using Excel for years, but I'm pretty new to PowerBI, so forgive me if I use the wrong vocabulary. I blurred out irrelevant data in my screenshots below to focus on the data relevant to my question.

We have a table that shows, among other things, pounds sold (lbs column), contribution margin (CM column), and CM/lb. Pounds sold and CM are taken directly from a database (or is it called table?). The CM/lb is a New Measure that was created that goes: CM/lb = DIVIDE('MFP Product Profitability'[CM],'MFP Product Profitability'[lbs]). This makes sense. But in our table, I noticed that CM/lb was simply being added up. We were getting ridiculous CM/lb metrics like $3.35 / lb. Every product in category a. is supposed to be less than $1 per pound. 

So in my tables, I changed the way it's presented from Sum to Average. Now I get something more in line with reality, but it's still not completely accurate. For example in category a., the CM/lb should be (790/1261)=$0.63 per pound not $0.56 per pound. The average column is taking an average of averages. 

Is there anyway to have it sum up all the CM within category a, then divide by all the lbs in category a, so that I get a true CM/lb metric?

Thanks ahead of time for any help.

[v-jiascu-msft · Accepted solution]

Hi @Anonymous ,

The cause is simple here. You created a Calculated Column instead of a Measure actually. Please check it again. Your formula can't work in a measure. Please refer to the following one.

CM/lb =
DIVIDE (
    SUM ( 'MFP Product Profitability'[CM] ),
    SUM ( 'MFP Product Profitability'[lbs] )
)

Best Regards,

[Anonymous]

Thank you. This solved my problem.