Calculating Average Price Per Ton

[rachaelnelson]

I have a table of data that list all product sales.  I have a calculated column that calculates the cost / ton.  When I put this column in a matrix, each line calculates correctly but when I roll up the data to a product category or aggregate of the whole, the data is the average of each line ($226.73) and not total cost / total ton ($232.85).

How do I get the correct calculation in my matrix and in a card visual?  I would like to add a slicer with some the additional fields to filter to a particular date range, product category, etc but cannot do so until I figure out how to get the correct aggregate calculations.

Here is sample data:

Product	Cost	Tons	Cost/Ton	Category
A	500	2	250	DA
A	475	2	237.5	DA
A	482	1.89	255.026455	DA
B	200	1	200	BF
B	210	0.9	233.3333333	BF
B	203	1.1	184.5454545	BF
	2070	8.89	226.7342072
			232.8458943

[Vvelarde]

@rachaelnelson

Hi, use this measure:

AVGCost-Tons = AVERAGEX(Table1,Table1[Cost/Ton])

Regards

Victor

Lima - Peru

[rachaelnelson · Accepted solution]

Thank you, Vvelarde.  Your measure gave me the same values I was receiving with my current calculated column but it got me thinking and I was able to solve using the following:

cost/ton = CALCULATE(SUM(Table1[COST])/SUM(Table1[Tons]))

[Vvelarde]

@rachaelnelson

Yeah, sorry i had a bad read of your problem.

I thought you need 226.83 instead of  232.85.

Regards

Victor

Lima - Peru