Forum Discussion

Fistachpl's avatar
Fistachpl
Icon for Helper III rankHelper III
5 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 c...
Help with DAX
  • mizan2390's avatar
    mizan2390
    5 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

mizan2390's avatar
mizan2390
Icon for Super User rankSuper User
5 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