Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
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.
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
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.
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
Good morning,
Any lead on how to proceed please? Thank you for your help.
Noelle
can you please put sample data or screen shots for the tables
Here is screen shot of the table (keys table)
'Product'
'Date'
'VariableCosts'
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
76 | |
63 | |
51 | |
47 |
User | Count |
---|---|
211 | |
82 | |
61 | |
61 | |
60 |