Forum Discussion
Row wise calculation
Hi prasadpatil020,
The problem is not the SUMX itself but how the row context / filters are evaluated (and sometimes duplicate rows) which makes the MAX() return unexpected values or cause double-counting.
Try below DAX, it creates a virtual table and then sums up the data.
@@test =
SUMX(
SUMMARIZE(
'Order Level Details',
'Order Level Details'[Order No],
"OrderVal", MAX('Order Level Details'[Tot. Order Value Gross])
),
[OrderVal]
)🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
If 'Order No.' is unique in your table, you can simply use:
@@test = SUM('Order Level Details'[Tot. Order Value Gross])
If there are duplicate order numbers and you want to sum only the maximum value per order, your measure is close, but you should check your data for duplicates
DAX
@@test =
SUMX(
VALUES('Order Level Details'[Order No.]),
MAX('Order Level Details'[Tot. Order Value Gross])
)
This will work if, for each 'Order No.', the MAX returns the correct value (which is the only value if there are no duplicates). But if your table has multiple rows per order with different 'Tot. Order Value Gross'
Thank you bhanu_gautam 😊