Calculation of total cost (cost * quantity) through an intersection table

[#1]

Hello,

I have a Model table which contains the list of the different products a company makes, but each Model is made with a specific quantity of different parts which have their own cost.
I wanted to get the total cost of a Model, or even 2 models together (they can use partially the same parts, but with equal or different quantities), so I simply made a statistics box that shows the cost total for the COST field.
However, as you can imagine, this returns the wrong value because it doesn't account for the quantities, and only counts a part once even if it's used by 2 models

I just don't know how to get the total cost value for one model (part1quantity * part1cost + part3quantity * part3cost + ... etc) and I'd like to use this expression across the dashboard.
I compiled a simple example to help you understand.

As you can see from the example, I want to be able to select the Model "m1" and get a "40" cost value, that actually did count the quantities
If I select the models "m1" AND "m2" I would then get the value 69 (40 for m1 + 29 for m2)

This would let me be able to get the total cost for each model, I just have no idea where to start? Should I compute this in the Script and make it act as a field of its own in PartModel ?

I thought maybe I could create a TotalCost field (in ModelPart) that simply took the quantity (also in the ModelPart table) and multiplied it by the Part cost (it would have to fetch it from the Part table, using the reference)

[tanelry · Accepted solution]

Have you tried pivot table with

dimensions: Name_Model, Name_Part

Qty expression: sum(Quantity)

Cost expression: sum(Quantity * Cost_Part)

?

[#3]

No I haven't tried it, I didn't think it was that simple!!
Thanks a lot