Difficulties with Columns and Measures

[Anonymous]

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

[HotChilli · Accepted 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'

[Anonymous]

Good explanation - thank you!!