Lookupvalue before calculate gross margin from variable costs

[Noelle]

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

[v-juanli-msft]

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]

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

[Noelle]

[v-juanli-msft]

Hi @Noelle 

Sorry, i don't know what you want finally and what's wrong you meet right now.

I have reproduce your data model and create a matrix below.

Please let me know what i need to do next step.

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]

Good Morning @v-juanli-msft, thank you for your help.

Two comments : sector would be in a different table than customer, & we are selling chemicals by kg so an order is (revenue+volume).

I have the same models that you do.

I now need to calculate :

- total revenue (CHF) : SUM(sales1[revenue])+SUM(sales2[revenue])

- total volume (KG) : SUM(sales1[volume])+SUM(sales2[volume])

- gross margin : [total revenue] - [total volume] * variable costs

This is what I have done in screenshots (I don't know how to share a pbix like you do), by adding the measure:

sum(cost[cost]) in your example. And since the report is additionning costs, you can see that I have negative values or always the same..

I hope this is more clear now.

Thank you in advance.

Noelle

[Noelle]

Good morning,

Any lead on how to proceed please? Thank you for your help.

Noelle

[amaniramahi]

can you please put sample data or screen shots for the tables

[Noelle]

Here is screen shot of the table (keys table)

'Product''Date''VariableCosts'