Value is applied per line item but should be applied to an order

[stino123]

Hi,

I extract data from shopify into Power BI. In doing so all my metrics work well except for shipping. In the below image you see shipping is applied to two items for one order. In reality the shipping is 5 total not 10. To compensate for this in dax I sum the shipping and devide it by the line items. However if a specific item is ordered twice this formula fails. Is there a way in dax or calculated column to have shipping only applied once per order. Rather than per line item. 

Thanks in advance,

[ReneMoawad · Accepted solution]

Create a New Column with the below formula

Shipping Cost per Line =
Var ShippingID = ShippingTable[ShippingID]
Var nbrOfLines = CALCULATE(
    COUNT(ShippingTable[ShippingID])
    , ALL(ShippingTable)
    , ShippingTable[ShippingID] = ShippingID
)
RETURN
DIVIDE(ShippingTable[Shipping] , nbrOfLines)

[stino123]

Hey @ReneMoawad this seemed to have worked! Thanks so much!

[ryan_mayu]

 Is there any way to distinguish betweendistinguish between these two scenarios?

could you pls paste the sample data ane expected output?

@stino123