Forum Discussion
Sum only Unique Value for Invoice Number
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 |
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.
6 Replies
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.
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
