Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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]))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |