Forum Discussion
Solved
Infinity issue with DIVIDE multiply and subtract
Hi All
The formula below works but with an infinity issue
Sales Total (Net) = SUMX('Invoices',('Invoices'[LineItem_UnitAmount]*'Invoices'[LineItem_Quantity] - 'Invoices'[CreditAmount] )/Invoices[LineItem_TaxAmount])
I have tried converting to a DIVIDE but can't seem to get it to work.
Any suggestions much apprecated
Thanks
Jack
Hi JackEnviro ,
Have you tried the following code:
ttt = SUMX ( 'Invoices', DIVIDE ( ( 'Invoices'[LineItem_UnitAmount] * 'Invoices'[LineItem_Quantity] - 'Invoices'[CreditAmount] ), Invoices[LineItem_TaxAmount] ) )If this doesn't work can you share some sample information and expected result?
Regards,
MFelix
2 Replies
Hi JackEnviro ,
Have you tried the following code:
ttt = SUMX ( 'Invoices', DIVIDE ( ( 'Invoices'[LineItem_UnitAmount] * 'Invoices'[LineItem_Quantity] - 'Invoices'[CreditAmount] ), Invoices[LineItem_TaxAmount] ) )If this doesn't work can you share some sample information and expected result?
Regards,
MFelix
Hi felix
Sorry thought that solved it but it, is not working but result 36 is not what is expected, should be £1957.34, or close to
The underlying table for that invoice look like this:
LineItem_Quantity LineItem_UnitAmount LineItem_ItemCode LineItem_TaxAmount LineItem_LineAmount InvoiceNumber SubTotal TotalTax Total AmountPaid AmountCredited Tax% CreditAmount 46 27.36 D-H-S-4 209.76 1258.56 EB13269 1957.34 392.05 2349.39 2349.39 0 1.2 0 181 2.39 DA-SP-10 72.4 432.59 EB13269 1957.34 392.05 2349.39 2349.39 0 1.2 0 5 20.99 DA-TC-B 17.5 104.95 EB13269 1957.34 392.05 2349.39 2349.39 0 1.2 0 35 18.57 DA-J-P-50 108.5 649.95 EB13269 1957.34 392.05 2349.39 2349.39 0 1.2 0 1 -123.65 -20.61 -123.65 EB13269 1957.34 392.05 2349.39 2349.39 0 1.2 0 1 26.99 DA-LC-B 4.5 26.99 EB13269 1957.34 392.05 2349.39 2349.39 0 1.2 LineItem_Quantity LineItem_UnitAmount LineItem_ItemCode LineItem_TaxAmount LineItem_LineAmount InvoiceNumber SubTotal TotalTax Total AmountPaid AmountCredited Tax% CreditAmount 46 27.36 D-H-S-4 209.76 1258.56 EB13269 1957.34 392.05 2349.39 2349.39 0 1.2 0 181 2.39 DA-SP-10 72.4 432.59 EB13269 1957.34 392.05 2349.39 2349.39 0 1.2 0 5 20.99 DA-TC-B 17.5 104.95 EB13269 1957.34 392.05 2349.39 2349.39 0 1.2 0 35 18.57 DA-J-P-50 108.5 649.95 EB13269 1957.34 392.05 2349.39 2349.39 0 1.2 0 1 -123.65 -20.61 -123.65 EB13269 1957.34 392.05 2349.39 2349.39 0 1.2 0 1 26.99 DA-LC-B 4.5 26.99 EB13269 1957.34 392.05 2349.39 2349.39 0 1.2 0 Thank
Jack
