Forum Discussion

prasadpatil020's avatar
prasadpatil020
Icon for Helper I rankHelper I
9 months ago
Solved

Row wise calculation

Hi Community..!! Order No. Tot. Order Value Gross Bus. Unit Code @@test 1549751713 33260 10 33260 1557865412 91944.46 10 91944.46 1557893444 31963 10 31963 i have above d...
Need Help
Show and Tell
  • grazitti_sapna's avatar
    grazitti_sapna
    9 months ago

    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!

     

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
9 months ago

prasadpatil020 ,

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'