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

- ProductID

- DateID

- SalesPrice

- Amount

 

The Purchasing table contains the following columns:

- ProductID

- DateID

- PurchasePrice

 

Both tables have a many to one relationship to the Product table via the ProductID column. They also have a many to one relationship to the Date table via the DateID column. 

I want to get the Contribution margin by subtracting the PurchasePrice from the SalesPrice. The two prices are matched by having the same DateID and ProductID. 

 

I would greatly appreciate any help with the measure.

Best regards,

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.

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super 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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Try this approach

    1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.
    2. Create a relationship (Many to One and Single) from the Date column of both tables to the Date column of the Calendar Table
    3. Create a thir table with all unique ProductID entries.
    4. Create a relationship (Many to One and Single) from the ProductID column of both tables to the ID columns of the Product ID table create in step 3 above
    5. To any visual/filter/slicer, drag any date dimension from the Calendar Table and the Product ID column from the table create ni step 3 above
    6. Write these measures

    S = sum(Sales[Salesprice])

    P = sum(Purchases[Purchaseprice])

    C = [S]-[P]

    C % = divide([C],[S])

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.