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 e...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
Noelle
Icon for Helper I rankHelper I
6 years ago

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's avatar
    Noelle
    Icon for Helper I rankHelper I
    6 years ago

    Good morning,

     

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

     

    Noelle