Need help calculating totals which keep duplicating values

[vicky_]

Hello, 

not sure if it's just forgetting how to do DAX after a couple of weeks of break or what, but I have a problem that I can't quite seem to solve. 
I have a sales, returns and order item-cost table. Please note - some of the tables are connected via DirectQuery, so I can't add columns. I'm having trouble calculating the total of the returned items-costs (which is the sum of each items (returns / ordered qty) * item's cost)

(using concat for illustration purposes, it is supposed to be a sumx)
my issue is that the totals values for when there are more than one items is duplicated. For the returned item costs of OrderA, I expect 5 + 10 = 15, but i'm getting 30 + 30 = 60 instead. 

I've attached a pbix below. 

Thank you in advance.

[xifeng_L · Accepted solution]

Hi @vicky_

The problem is in the costadj variable. When subtotaling rows, VALUES(Orders[ItemID]) gets all ItemIDs under the current OrderID, not the ItemID of the current row.

Here is the corrected Measure:

returned item costs = 
CONCATENATEX(
    VALUES(Orders[ItemID]), 
        var ratio = CALCULATE(DIVIDE([Return Qty (measure)], SUM(Orders[OrderQty]), 0))
        var costadj = CALCULATE(SUM('Order Items Cost'[Cost]) * ratio, TREATAS({Orders[ItemID]}, 'Order Items Cost'[Item])) 
        RETURN costadj
    , ", ")

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[vicky_]

That worked like a charm. Thanks so much!