Forum Discussion
Solved
Multiplication across tables
Hello,
I have two simple tables, one which has car components and their prices; and the other has cars, their components and the number of components. They have a relationship at item.
How do I compute the the total price of fixing a Camry? Or just fixing its tyres? The closest I've come is making this matrix.
Any help would be appreciated.
- Couple of ways.
Repair Cost SuMX = SUMX(RepairTable,[No]*AVERAGE(CostTable[Price])
But this is costly in terms of resources. I avoid SUMX whenever possible
Add a Calculted Column for Extended Cost in the Repair Table
Extended Cost = RELATED(CostTable[Price])*[No]
Then a measure
Total Cost = SUM( ‘Repair Table’[Extended Cost])
Now build matrix with [Car] and [Total Cost]
2 Replies
- Couple of ways.
Repair Cost SuMX = SUMX(RepairTable,[No]*AVERAGE(CostTable[Price])
But this is costly in terms of resources. I avoid SUMX whenever possible
Add a Calculted Column for Extended Cost in the Repair Table
Extended Cost = RELATED(CostTable[Price])*[No]
Then a measure
Total Cost = SUM( ‘Repair Table’[Extended Cost])
Now build matrix with [Car] and [Total Cost]The first measure doesn't produce the correct results and I'm not sure why it would use an average. However the second solution works perfectly and was exactly what I was looking for, thank you =).
