Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LeahGJ
Frequent Visitor

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 NamePayment DatePayment AmountPayment MethodCheque #Invoice NumberInvoice DatePO Number Payment Amount for the Invoice  Invoice Amount Line Type Line Distribution Amount Line Number
Supplier XThursday, 5 July 2018423660Check234218‭10‬Tuesday, 8 November 2016‭414108‬     337,406.25  6,525,000.00ITEM  5,800,000.001
Supplier XTuesday, 29 May 2018285000Check232866‭10‬Tuesday, 8 November 2016‭414108‬     285,000.00  6,525,000.00ITEM  5,800,000.001
Supplier XMonday, 7 May 2018150000Check231951‭10‬Tuesday, 8 November 2016‭414108‬     150,000.00  6,525,000.00ITEM  5,800,000.001
Supplier XMonday, 16 April 20181000000Check231313‭10‬Tuesday, 8 November 2016‭414108‬  1,000,000.00  6,525,000.00ITEM  5,800,000.001
Supplier XThursday, 29 March 2018650000Check230742‭10‬Tuesday, 8 November 2016‭414108‬     650,000.00  6,525,000.00ITEM  5,800,000.001
Supplier XTuesday, 6 June 20171247906.25Check216716‭10‬Tuesday, 8 November 2016‭414108‬  1,247,906.25  6,525,000.00ITEM  5,800,000.001
Supplier XTuesday, 20 December 20161223437.5Check211059‭10‬Tuesday, 8 November 2016‭414108‬  1,223,437.50  6,525,000.00ITEM  5,800,000.001
Supplier XFriday, 9 December 20161631250Check209361‭10‬Tuesday, 8 November 2016‭414108‬  1,631,250.00  6,525,000.00ITEM  5,800,000.001
Supplier XFriday, 9 December 2016-1631250Check209360‭10‬Tuesday, 8 November 2016‭414108‬- 1,631,250.00- 6,525,000.00ITEM- 5,800,000.001
Supplier XFriday, 9 December 20161631250Check209360‭10‬Tuesday, 8 November 2016‭414108‬  1,631,250.00  6,525,000.00ITEM  5,800,000.001
Supplier XThursday, 5 July 2018423660Check234218‭10‬Tuesday, 8 November 2016‭414108‬     337,406.25  6,525,000.00TAX     725,000.002
Supplier XTuesday, 29 May 2018285000Check232866‭10‬Tuesday, 8 November 2016‭414108‬     285,000.00  6,525,000.00TAX     725,000.002
Supplier XMonday, 7 May 2018150000Check231951‭10‬Tuesday, 8 November 2016‭414108‬     150,000.00  6,525,000.00TAX     725,000.002
Supplier XMonday, 16 April 20181000000Check231313‭10‬Tuesday, 8 November 2016‭414108‬  1,000,000.00  6,525,000.00TAX     725,000.002
Supplier XThursday, 29 March 2018650000Check230742‭10‬Tuesday, 8 November 2016‭414108‬     650,000.00  6,525,000.00TAX     725,000.002
Supplier XTuesday, 6 June 20171247906.25Check216716‭10‬Tuesday, 8 November 2016‭414108‬  1,247,906.25  6,525,000.00TAX     725,000.002
Supplier XTuesday, 20 December 20161223437.5Check211059‭10‬Tuesday, 8 November 2016‭414108‬  1,223,437.50  6,525,000.00TAX     725,000.002
Supplier XFriday, 9 December 20161631250Check209361‭10‬Tuesday, 8 November 2016‭414108‬  1,631,250.00  6,525,000.00TAX     725,000.002
Supplier XFriday, 9 December 2016-1631250Check209360‭10‬Tuesday, 8 November 2016‭414108‬- 1,631,250.00- 6,525,000.00TAX-    725,000.002
Supplier XFriday, 9 December 20161631250Check209360‭10‬Tuesday, 8 November 2016‭414108‬  1,631,250.00  6,525,000.00TAX     725,000.002
1 ACCEPTED 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!

View solution in original post

6 REPLIES 6
Fowmy
Super User
Super User

@LeahGJ 

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

LeahGJ
Frequent Visitor

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. 

LeahGJ
Frequent Visitor

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 #ITEMTAXGrand Total
105,800,000.00725,000.006,525,000.00
12112,500.0011,250.00123,750.00
Total5,912,000.00736,250.006,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!

LeahGJ
Frequent Visitor

The formula worked partially. the grand total is off. 

Please, have a look at this topic:

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

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors