Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
@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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
@LeahGJ
What is the expected result you need from the data above?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
@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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
The formula worked partially. the grand total is off.
Please, have a look at this topic:
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |