Forum Discussion
Helper IIIPreparing a pricelist - products and assemblies
This is a great example of where the challenge is less about the formula itself and more about how the model is structured.
For assemblies, what you’re really trying to do is iterate through each component in the assembly, multiply the quantity by the product price, and then sum everything up.
In DAX terms, that usually means using something like SUMX over your assembly configuration table, where you can pull in the related product price for each component.
Conceptually, it would look something like:
Loop through each row in the assembly configuration
For each component:
get the quantity
get the related product price
Multiply and sumSo your assembly part of the measure would follow that pattern:
Assembly Price =
SUMX(
'Assembly configurations',
'Assembly configurations'[Quantity] *
RELATED('Basic product pricelist'[New Price])
)Then your SWITCH logic can call this when Type = "Assembly".
If this isn’t returning results yet, it’s usually a sign that the relationship path between:
assembly → configuration → product → price
isn’t fully connected or active in the model.
One thing I’d double check is whether your model allows the filter from the selected assembly to correctly flow into the configuration table and then back to the product price. That relationship flow is what makes the calculation work.
I’ve run into similar setups where the DAX looked right, but the real issue was the relationship direction or missing link in the chain.
Super Userhi Fistachpl
can you try out this DAX
_price =
// 1. Wrap the entire calculation in SUMX to ensure the Grand Total row calculates correctly
SUMX(
VALUES('dim Products and Assemblies'[Catalogue No]),
// 2. Identify if the current row is a Product or an Assembly
VAR _CurrentType = CALCULATE( MAX('dim Products and Assemblies'[Type]) )
// 3. Calculation for Basic Products (Uses your existing measure)
VAR _ProductPrice = [_basicProductPrice]
// 4. Calculation for Assemblies
VAR _AssemblyPrice =
SUMX(
'Assembly configurations',
// Multiply the amount by the looked-up price of the component
'Assembly configurations'[Amount] *
LOOKUPVALUE(
'Basic product pricelist'[New Price],
'Basic product pricelist'[Product cat No], 'Assembly configurations'[Product cat no]
)
)
// 5. Output the correct calculation based on the Type
RETURN
SWITCH(
TRUE(),
_CurrentType = "Product", _ProductPrice,
_CurrentType = "Assembly", _AssemblyPrice
)
)If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread