SUMIF Help within one table

[garretc]

Hi all,

Hope you can help me. I have a dataset which contains thousands of rows of invoice data. It may contain more than one row per invoice (because it's across multiple cost centres). I am analysing invoice volumes by Supplier and Supplier Categories. I would like to be able to filter so that I can show only those invoices with a total net value of less than, say, $25. The purpose is to analyse where the in-house processing cost might be higher than the actual goods/services being procured. 

I am using Tree Graphs with [Supplier Category] as the Category, [Supplier] as the details and Distinct Count of [Invoice ID] as the value. However, I believe that when I set a slicer on the dashboard against [Net Amount] and set it to show all values <$25, it is removing only suppliers/supplier categories where the total is >$25, not at the unique invoice ID level.

So I'm thinking that I need to create a column that flags whether an invoice total (not invoice line) is less than $25. And to do that, I need to calculate a cumulative total based on the invoice ID. If I were in Excel, I'd simply do SUMIF([Invoice ID],[@[Invoice ID]],[Net Amount]) but I can't seem to figure out an equivalent here.

I guess I could create a separate table out of the data, and point the dashboards to here, but it feels a bit inelegant.

Invoice ID	Supplier	Supplier Category	Cost Centre	Net Amount
1	ACME	Products	ABC	$                15.00
1	ACME	Products	DEF	$                16.00
1	ACME	Products	GHI	$                17.00
2	Rollercoaster Manufacturing	Products	ABC	$             100.00
3	Boomtown Industries	Products	DEF	$                  2.00
4	Distributed Goods	Transport	GHI	$                50.00
5	Import Export Limited	Products	ABC	$       10,000.00
6	Eagle Inc	Transport	ABC	$                  5.00

And below is the dataset with what I desire to see:

Invoice ID	Supplier	Supplier Category	Cost Centre	Net Amount	Total Invoice Amt	<$25?
1	ACME	Products	ABC	$                15.00	$                 48.00	FALSE
1	ACME	Products	DEF	$                16.00	$                 48.00	FALSE
1	ACME	Products	GHI	$                17.00	$                 48.00	FALSE
2	Rollercoaster Manufacturing	Products	ABC	$             100.00	$               100.00	FALSE
3	Boomtown Industries	Products	DEF	$                  2.00	$                   2.00	TRUE
4	Distributed Goods	Transport	GHI	$                50.00	$                 50.00	FALSE
5	Import Export Limited	Products	ABC	$       10,000.00	$         10,000.00	FALSE
6	Eagle Inc	Transport	ABC	$                  5.00	$                   5.00	TRUE

[Greg_Deckler · Accepted solution]

@garretc Try these:

Total Invoice Amt Column = 
    VAR __InvoiceID = [Invoice ID]
    VAR __Table = FILTER('Table', [Invoice ID] = __InvoiceID)
    vAR __Result = SUMX(__Table, [Net Amount])
RETURN
    __Result

< $25 Column = IF([Total Invoice Amt] > 50, TRUE(), FALSE())

[garretc]

@Greg_Deckler Thanks so much for taking the time to look into this and reply - this worked a charm!