Sum only Unique Value for Invoice Number

LeahGJ — Sun, 25 Jul 2021 04:47:02 GMT

Hi,

I have a dataset, with duplicate data because of multiple lines. I want to sum the value of the invoice amount paid. I used to be able to do same, by using the "line Districtuion Amount" column. however, i have a variable that i can't figure out. for instance, invoice number "10" totalling $6,525,000.00, was paid, but then cancelled, then paid in 8 incremental payments. the Line distribution is duplicating the value.

The data set, also itemises the data by tax and non-tax amounts, with the tax being $725,000.00, and non-tax $5,800,000.00.

Supplier Name	Payment Date	Payment Amount	Payment Method	Cheque #	Invoice Number	Invoice Date	PO Number	Payment Amount for the Invoice	Invoice Amount	Line Type	Line Distribution Amount	Line Number
Supplier X	Thursday, 5 July 2018	423660	Check	234218	‭10‬	Tuesday, 8 November 2016	‭414108‬	337,406.25	6,525,000.00	ITEM	5,800,000.00	1
Supplier X	Tuesday, 29 May 2018	285000	Check	232866	‭10‬	Tuesday, 8 November 2016	‭414108‬	285,000.00	6,525,000.00	ITEM	5,800,000.00	1
Supplier X	Monday, 7 May 2018	150000	Check	231951	‭10‬	Tuesday, 8 November 2016	‭414108‬	150,000.00	6,525,000.00	ITEM	5,800,000.00	1
Supplier X	Monday, 16 April 2018	1000000	Check	231313	‭10‬	Tuesday, 8 November 2016	‭414108‬	1,000,000.00	6,525,000.00	ITEM	5,800,000.00	1
Supplier X	Thursday, 29 March 2018	650000	Check	230742	‭10‬	Tuesday, 8 November 2016	‭414108‬	650,000.00	6,525,000.00	ITEM	5,800,000.00	1
Supplier X	Tuesday, 6 June 2017	1247906.25	Check	216716	‭10‬	Tuesday, 8 November 2016	‭414108‬	1,247,906.25	6,525,000.00	ITEM	5,800,000.00	1
Supplier X	Tuesday, 20 December 2016	1223437.5	Check	211059	‭10‬	Tuesday, 8 November 2016	‭414108‬	1,223,437.50	6,525,000.00	ITEM	5,800,000.00	1
Supplier X	Friday, 9 December 2016	1631250	Check	209361	‭10‬	Tuesday, 8 November 2016	‭414108‬	1,631,250.00	6,525,000.00	ITEM	5,800,000.00	1
Supplier X	Friday, 9 December 2016	-1631250	Check	209360	‭10‬	Tuesday, 8 November 2016	‭414108‬	- 1,631,250.00	- 6,525,000.00	ITEM	- 5,800,000.00	1
Supplier X	Friday, 9 December 2016	1631250	Check	209360	‭10‬	Tuesday, 8 November 2016	‭414108‬	1,631,250.00	6,525,000.00	ITEM	5,800,000.00	1
Supplier X	Thursday, 5 July 2018	423660	Check	234218	‭10‬	Tuesday, 8 November 2016	‭414108‬	337,406.25	6,525,000.00	TAX	725,000.00	2
Supplier X	Tuesday, 29 May 2018	285000	Check	232866	‭10‬	Tuesday, 8 November 2016	‭414108‬	285,000.00	6,525,000.00	TAX	725,000.00	2
Supplier X	Monday, 7 May 2018	150000	Check	231951	‭10‬	Tuesday, 8 November 2016	‭414108‬	150,000.00	6,525,000.00	TAX	725,000.00	2
Supplier X	Monday, 16 April 2018	1000000	Check	231313	‭10‬	Tuesday, 8 November 2016	‭414108‬	1,000,000.00	6,525,000.00	TAX	725,000.00	2
Supplier X	Thursday, 29 March 2018	650000	Check	230742	‭10‬	Tuesday, 8 November 2016	‭414108‬	650,000.00	6,525,000.00	TAX	725,000.00	2
Supplier X	Tuesday, 6 June 2017	1247906.25	Check	216716	‭10‬	Tuesday, 8 November 2016	‭414108‬	1,247,906.25	6,525,000.00	TAX	725,000.00	2
Supplier X	Tuesday, 20 December 2016	1223437.5	Check	211059	‭10‬	Tuesday, 8 November 2016	‭414108‬	1,223,437.50	6,525,000.00	TAX	725,000.00	2
Supplier X	Friday, 9 December 2016	1631250	Check	209361	‭10‬	Tuesday, 8 November 2016	‭414108‬	1,631,250.00	6,525,000.00	TAX	725,000.00	2
Supplier X	Friday, 9 December 2016	-1631250	Check	209360	‭10‬	Tuesday, 8 November 2016	‭414108‬	- 1,631,250.00	- 6,525,000.00	TAX	- 725,000.00	2
Supplier X	Friday, 9 December 2016	1631250	Check	209360	‭10‬	Tuesday, 8 November 2016	‭414108‬	1,631,250.00	6,525,000.00	TAX	725,000.00	2

Re: Sum only Unique Value for Invoice Number

Fowmy — Sun, 25 Jul 2021 07:10:12 GMT

LeahGJ

What is the expected result you need from the data above?

Re: Sum only Unique Value for Invoice Number

LeahGJ — Sun, 25 Jul 2021 13:27:08 GMT

Hi,

the expected result is to show only the invoice amount and correct grand totals for both "ITEM" and "TAX".

The full data set has multiple invoices against the PO number. The grand totoal for the invoices against the PO is $11Mn. however, with the multiple payments to the indivisal invoice "10", the value is totalling as $49Mn.

Re: Sum only Unique Value for Invoice Number

LeahGJ — Sun, 25 Jul 2021 13:32:54 GMT

the total invoice amount is $6,525,000.00, of which tax is 725,00.00. therefore i will for Power BI to show the results as follows:


Invoice #	ITEM	TAX	Grand Total
10	5,800,000.00	725,000.00	6,525,000.00
12	112,500.00	11,250.00	123,750.00
Total	5,912,000.00	736,250.00	6,648,250.00

Re: Sum only Unique Value for Invoice Number

ERD — Mon, 26 Jul 2021 07:01:01 GMT

LeahGJ ,

you might need something like this:

measure_Item = VAR currentInvNum = MAX ( T[Invoice Number] ) VAR ItemLineType = "ITEM" RETURN CALCULATE ( MAX ( T[Line Distribution Amount] ), FILTER ( ALL ( T ), T[Invoice Number] = currentInvNum && T[Line Type] = ItemLineType ) )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

Re: Sum only Unique Value for Invoice Number

LeahGJ — Fri, 30 Jul 2021 15:32:49 GMT

The formula worked partially. the grand total is off.

Re: Sum only Unique Value for Invoice Number

ERD — Fri, 30 Jul 2021 17:53:04 GMT

Please, have a look at this topic:

https://community.powerbi.com/t5/Desktop/DAX-Measure-Correct-values-but-wrong-total/m-p/1790203#M699236

topic Re: Sum only Unique Value for Invoice Number in DAX Commands and Tips

Sum only Unique Value for Invoice Number

Re: Sum only Unique Value for Invoice Number

Re: Sum only Unique Value for Invoice Number

Re: Sum only Unique Value for Invoice Number

Re: Sum only Unique Value for Invoice Number

Re: Sum only Unique Value for Invoice Number

Re: Sum only Unique Value for Invoice Number