Forum Discussion
SUMX issue
Please try this one. It gets the same results on rows and correct total. This is the more logical way I should have written it in the first place.
New SUMX =
SUMX (
SUMMARIZE ( Invoice_items, Invoices[Inv_No], Invoice_items[Product] ),
CALCULATE (
DIVIDE ( SUM ( Receipts[Amount] ), SUM ( Invoices[Amount] ) )
* SUM ( Invoice_items[Amount] )
)
)If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi mahoneypat,
If I select a Product (product B in the below example), the rows on the table below will not tot up the the total value. It is actually the rows that are correct but the total is wrong. It is the same total as appearing on the Product B row in the upper table, but this is also wrong.
The reason for this (I believe) is that I'm using the [Inv % Paid] measure within my new measure, which calculates at invoice level and not invoice_items level and therefore if I just use a simple SUM it will not be an iterative calculation across each invoice when totalling.
Total of SumX Measure should be 5,283.20 as that is the sum of the rows.
Microsoft EmployeePlease try this one. It gets the same results on rows and correct total. This is the more logical way I should have written it in the first place.
New SUMX =
SUMX (
SUMMARIZE ( Invoice_items, Invoices[Inv_No], Invoice_items[Product] ),
CALCULATE (
DIVIDE ( SUM ( Receipts[Amount] ), SUM ( Invoices[Amount] ) )
* SUM ( Invoice_items[Amount] )
)
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
😁YES! This has worked a treat for me. I'd be scared to tot up all the hours I spent on this!
Thanks you mahoneypat, much appreciated
If only I could hit the kudos button more than once!