topic Need help calculating totals which keep duplicating values in DAX Commands and Tips

Need help calculating totals which keep duplicating values

vicky_ — Thu, 09 Jan 2025 06:17:49 GMT

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.

Re: Need help calculating totals which keep duplicating values

xifeng_L — Thu, 09 Jan 2025 09:29:03 GMT

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~

Re: Need help calculating totals which keep duplicating values

vicky_ — Thu, 09 Jan 2025 21:18:32 GMT

That worked like a charm. Thanks so much!