Forum Discussion
Calculating Average Price Per Ton
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 |
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]))
3 Replies
Hi, use this measure:
AVGCost-Tons = AVERAGEX(Table1,Table1[Cost/Ton])
Regards
Victor
Lima - Peru
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]))
Yeah, sorry i had a bad read of your problem.
I thought you need 226.83 instead of 232.85.
Regards
Victor
Lima - Peru
