Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi
I'm trying to do some pretty standard stuff involving goods orders and shipping costs, with the shipping costs varying according to Order class, and consisting of a flat fee plus a percentage of the order value
I've got 4 tables set up like this:
The desktop visualisation is at https://1drv.ms/u/s!AmxJyApgEAcYgtUnlUjfJnmQhIk5qw
I want to produce a table showing sales£ + delivery charges by customer. It's easy to do this if I add a new custom column:
However i've not been able to calculate Shipping charges using a measure. I've tried going back to basic and just calculating the variable part of the shipping charge by:
Variable Shipping Charge = SUM(Sales[Sale£])*sum(Order_Classes[PC Shipping Charge])
But this gives erroneous values (MiniMart's Shipping charge, for example, should be a straight 10% of the Sale£.)
I've tried adding RELATED into the formula but it doesn't work
I've had no joy in calculating the fixed charge component of the shipping fee at all. Here' my latest attempt:
NOte that the "F" I typed in hasn't prompted anything from Intellisense. If I type in a legitimate data name, it rejects it.
So - what am I doing wrong?
Thanks for your help
Solved! Go to Solution.
Hi,
The first thing we need to clear up is that RELATED can't be used unless you have row context, so it needs a calculated column or an iterator like SUMX.
The lack of intellisense when you typed in your formula is powerbi's way of telling you what fields you can and can't use.
The other issue that is causing problems in your model is that the relationship between Order Classes and Sales is one way. That is good practice when you want to slice and dice your sales by Order Class. However, you are trying to retrieve values from OrderClass according to what you have in Sales. That's why you don't get the correct value for the Variable Shipping Charge (powerbi can't navigate the relationship so it just summed the values in OrderClass).
Your options?
Make the relationship between Sales and Order Class bi-directional
OR rewrite the measures to start from the OrderClass side e.g.
MVariable Shipping Charge = SUMX(Order_Classes, Order_Classes[PC Shipping Charge] * Sales[Sales Total] )
OR using Power Query, edit your query to combine the Sales and Order_Classes table (keeping only what information you need). This is less 'relational' and more 'dimensional'
Hi,
The first thing we need to clear up is that RELATED can't be used unless you have row context, so it needs a calculated column or an iterator like SUMX.
The lack of intellisense when you typed in your formula is powerbi's way of telling you what fields you can and can't use.
The other issue that is causing problems in your model is that the relationship between Order Classes and Sales is one way. That is good practice when you want to slice and dice your sales by Order Class. However, you are trying to retrieve values from OrderClass according to what you have in Sales. That's why you don't get the correct value for the Variable Shipping Charge (powerbi can't navigate the relationship so it just summed the values in OrderClass).
Your options?
Make the relationship between Sales and Order Class bi-directional
OR rewrite the measures to start from the OrderClass side e.g.
MVariable Shipping Charge = SUMX(Order_Classes, Order_Classes[PC Shipping Charge] * Sales[Sales Total] )
OR using Power Query, edit your query to combine the Sales and Order_Classes table (keeping only what information you need). This is less 'relational' and more 'dimensional'
Good explanation - thank you!!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
10 | |
10 | |
10 | |
10 |
User | Count |
---|---|
19 | |
14 | |
13 | |
11 | |
8 |