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.

     

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.