Forum Discussion
vicky_
Super User
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-cos...
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~
xifeng_LSuper User
Super UserHi 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_Super User
Super UserThat worked like a charm. Thanks so much!