Forum Discussion

andbeh's avatar
andbeh
New Member
2 years ago
Solved

Calculate Contribution margin using Sales and Purchase tables

Hello,    I'm trying to calculate the Contribution margin by taking the sales data from one table and the purchasing data from another.  The Sales table contains the following columns: - OrderID ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi andbeh ,

     

    Based on your description, I created the following data model. Please refer to the following dax formula.

    Result =
CALCULATE (
    MAX ( Sales[SalesPrice] ),
    FILTER (
        ALL ( Sales ),
        Sales[ProductID] = MAX ( 'Product'[ProductID] )
            && Sales[DateID] = MAX ( Sales[DateID] )
    )
)
    - CALCULATE (
        MAX ( Purchasing[PurchasePrice] ),
        FILTER (
            ALL ( Purchasing ),
            Purchasing[DateID] = MAX ( Purchasing[DateID] )
                && Purchasing[ProductID] = MAX ( Purchasing[ProductID] )
        )
    )

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

andbeh , It is better to add Purchase price at line level of Sales at time ETL or as calculated column or using merge table in Power query 

 

New column in DAX in sales table

maxx(filter(purchase, purchase[Product Id] = sales[Product ID] && purchase[DateID] = Sales[DateID]), [purchase price])

 

In case of Powrr query

select sales table, merge queries

Select purchase table, ctrl + click, and select two columns

While expanding take max the purchase price (take the if needed)

 

Merge Tables (Power Query) : https://youtu.be/zNrmbagO0Oo