Forum Discussion

Fistachpl's avatar
Fistachpl
Helper III
4 months ago
Solved

Sales through sets (assemblies)

Hello,

 

I have a problem and I am not sure if I have my data model correct + need help with dax. 

 

I have few tables: 

1) Pricelist with products - New pricelist:

2) Assemblies (which consist of the Products from table 1)) - TAssemblies

3) The Assembly configurations (here is information what amount of products is part of each assembly) - Assembly configurations

4) I create a dim Products and Assemblies table, where all items are listed (with additional information if it is a product or an assembly of products) - dim Products and assemblies

5) Sales table with information when, which product was sold in which amount - TSales

I create a calendar table Calendar, and create a relationships between tables:

 

I can easily calculate the price of an assembly:

Right now I want to calculate the Turnover I made with my sales: 

To calculate the turnover I need to:

 

If Type = Product 

get the price of each product and calculate it by amount sold\

else

I need to calculate the price of the assembly by multiplying the price of each product * amount of product in this assembly * the amount of assemblies sold for which the Assembly no = Product or Assembly, (only for Type = Assembly)

 

How should I do it? 

 

I mean I don't know how to write the DAX (it is probably easier than I suspect ;)) but also want to make sure my data model is correct and therefore I ask for Your help.

 

I know I can modify the TSales table to remove the assemblies from there and input the products from the assembly but I would like to avoid it (during querying) - or it would be recommended I do this through edyting the query in such a way?

 

Please find the demo file here: Calculating the sales through assemblies.pbix

 

 

Help with DAX
  • mizan2390's avatar
    mizan2390
    4 months ago

    hi Fistachpl 

    Your data model is conceptually correct, and you do not need to flatten or modify your TSales table in Power Query.
    Keeping your assemblies and products combined in the TSales table is actually the best for this scenario. It accurately reflects the reality of your business (i.e., you sold an assembly as a single invoice line, not as scattered individual components). 
    To calculate your turnover dynamically, to apply the correct price measure ([_assemblyPrice] or [_currentPrice]) depending on the type of the item being sold, I tried with this DAX.
    Total Turnover = 
SUMX (
    'dim Products and assemblies',
    // 1. Calculate the total quantity sold for the current item
    VAR _SoldQuantity = CALCULATE ( SUM ( TSales[Amount] ) )
    
    RETURN
    // 2. Only perform the price calculation if the item was actually sold
    IF (
        NOT ISBLANK ( _SoldQuantity ),
        
        // 3. Determine the correct price based on the item Type
        VAR _ItemPrice = 
            IF (
                'dim Products and assemblies'[Type] = "Assembly",
                [_assemblyPrice],
                [_currentPrice]
            )
            
        // 4. Multiply quantity by the correct price
        RETURN
            _SoldQuantity * _ItemPrice
    )
)

    If this helped, please consider giving kudos and mark as a solution

    @me in replies or I'll lose your thread

1 Reply

  • mizan2390's avatar
    mizan2390
    Super User
    4 months ago

    hi Fistachpl 

    Your data model is conceptually correct, and you do not need to flatten or modify your TSales table in Power Query.
    Keeping your assemblies and products combined in the TSales table is actually the best for this scenario. It accurately reflects the reality of your business (i.e., you sold an assembly as a single invoice line, not as scattered individual components). 
    To calculate your turnover dynamically, to apply the correct price measure ([_assemblyPrice] or [_currentPrice]) depending on the type of the item being sold, I tried with this DAX.
    Total Turnover = 
SUMX (
    'dim Products and assemblies',
    // 1. Calculate the total quantity sold for the current item
    VAR _SoldQuantity = CALCULATE ( SUM ( TSales[Amount] ) )
    
    RETURN
    // 2. Only perform the price calculation if the item was actually sold
    IF (
        NOT ISBLANK ( _SoldQuantity ),
        
        // 3. Determine the correct price based on the item Type
        VAR _ItemPrice = 
            IF (
                'dim Products and assemblies'[Type] = "Assembly",
                [_assemblyPrice],
                [_currentPrice]
            )
            
        // 4. Multiply quantity by the correct price
        RETURN
            _SoldQuantity * _ItemPrice
    )
)

    If this helped, please consider giving kudos and mark as a solution

    @me in replies or I'll lose your thread