Forum Discussion

Noelle's avatar
Noelle
Icon for Helper I rankHelper I
6 years ago

Lookupvalue before calculate gross margin from variable costs

Hi all,

 

I have been struggling with this for a while now. I would like to calculate gross margin on revenue with the following setup:

- 1 table 'Date' connected to:

- 2 sources of orders with exact date & product code, where revenue is SUM(SALES[revenue]) + SUM('OPEN SALES'[revenue])

- 1 table 'Variable Costs' per month and per product range

- 1 table 'Products' to link a product code (in orders) to a product range, connected to 'Variable Costs', 'Sales', and 'Open Sales'

The idea is to calculate GM per product range, or geography, or sector, but I cannot do that with a single measure unless I can have, maybe, a lookupvalue for each single line of order.

 

Thank you in advance for your help!

Best regards,

noelle

Need Help

8 Replies

    • Noelle's avatar
      Noelle
      Icon for Helper I rankHelper I
      6 years ago
       

      Here is screen shot of the table (keys table)

      'Product''Date''VariableCosts'

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Noelle 

    Check if my pbix satisfy your needs.

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Noelle's avatar
      Noelle
      Icon for Helper I rankHelper I
      6 years ago

      Good Morning Maggie v-juanli-msft

       

      I have tried your connexion and measure.

      Variable costs are expressed per product range, and not product code, so I cannot have a 1:* relation between 'product' and 'variable costs'. product range is a group from product code. Let me prepare a sample for you, I guess that will be better than my previous screenshots.

       

      Best regards,

      Noelle