Forum Discussion
Super UserNeed help calculating totals which keep duplicating values
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.
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~
2 Replies
- xifeng_L
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!