Infinity issue with DIVIDE multiply and subtract

[JackEnviro]

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 

[MFelix · Accepted solution]

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

[JackEnviro]

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