Preparing a pricelist - products and assemblies

[Fistachpl]

Hello,

i have a problem as I want to prepare a simple pricelist but the problem is complex for me which is driving me mad a bit 😉

I have Tables with data: 

1)

with prices for basic products. whcih looks like this:

But I sell also assemblies so I have more tables:

2) Tassemblies which has the catalog numbers and names of each assembly:

3) Assembly configurations which have the information which basic product in which amount is part of an assembly:

With the Basic product pricelist and TAssemblies I prepare one dim Product and Assembly table:

in which I have all basic products, all assemblies (names and catalog numbers) and information if it is an assembly or product:

I remove the TAssemblies from the data model as I (suppose) no longer need it.

Now the relationships:

All are one way from dim to other tables:

I have 2 relationships with Assembly configurations because in future I (suppose) will need it for price calculations.

Now I want to prepare the pricelist.

For Type = Product it is simple as I have a relationship active:

_basicProductPrice =

    MAX('Basic product pricelist'[New Price])

and Now I want to have it in pricelist measure that calculates the price of basic products and assemblies based on type = Product or type = Assembly:

_price =

    SWITCH(

        TRUE(),

        MAX('dim Products and Assemblies'[Type])="Product", [_basicProductPrice],

        MAX('dim Products and Assemblies'[Type])="Assembly", 1

    )

but of course the price of the assembly is not 1 - I just put it there because I can't get this price. But in table visual it looks like this:

How can I now calculate the price of each assembly? 

e.g. for A3: 

I need to get the prices of all these 3 products in specified amounts. I can't even display the name of the product - I think I not only need DAX but I miss the relationship as well maybe?

How do I do it? 

Thank You very much for help in advance!

Thank You for Your help. 

[v-ssriganesh]

Hello @Fistachpl,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[Poojara_D12]

Hi @Fistachpl 

_price :=
VAR _type =
    SELECTEDVALUE('dim Products and Assemblies'[Type])

RETURN
SWITCH(
    TRUE(),

    -- Basic product
    _type = "Product",
        MAX('Basic product pricelist'[New Price]),

    -- Assembly
    _type = "Assembly",
        SUMX(
            'Assembly configurations',
            'Assembly configurations'[Quantity] *
            CALCULATE(
                MAX('Basic product pricelist'[New Price]),
                TREATAS(
                    VALUES('Assembly configurations'[Component Product]),
                    'Basic product pricelist'[Product]
                )
            )
        )
)

try this

[mizan2390]

hi @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

[v-ssriganesh]

Hi @Fistachpl,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @donbuser & @johnt75  for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[donbuser]

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 sum

So 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.

[Fistachpl]

@johnt75 thanks for the help but it doesn't work 😞

Sorry I forgot to include the link to the file:  Basic pricelist.pbix

[johnt75]

Bit more complicated as you need to incorporate amount too.

_price = SUMX(
    'dim Products and Assemblies',
    SWITCH(
        'dim Products and Assemblies'[Type],
        "Product", [_pprice],
        "Assembly", 
        VAR _BaseTable = CALCULATETABLE(
            SELECTCOLUMNS(
                'Assembly configurations',
                'Assembly configurations'[Amount],
                "@price",
                LOOKUPVALUE(
                    'Basic product pricelist'[New Price],
                    'Basic product pricelist'[Product cat No],
                    'Assembly configurations'[Product cat no]
                )
            )
        )
        VAR Result = SUMX( _BaseTable, [@price] * 'Assembly configurations'[Amount] )
        RETURN Result
    )
)

[johnt75]

For the Assembly types you need to move the filter from Assembly Configurations to the basic product pricelist, you can use TREATAS to do that.

_price =
SUMX (
    'dim Products and Assemblies',
    SWITCH (
        'dim Products and Assemblies'[Type],
        "Product", [_basicProductPrice],
        "Assembly",
            CALCULATE (
                [_basicProductPrice],
                TREATAS (
                    CALCULATETABLE ( VALUES ( 'Assembly configurations'[Product cat no] ) ),
                    'Basic Product Pricelist'[Product cat no]
                )
            )
    )
)