Forum Discussion
SUMIF Help within one table
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 |
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())
2 Replies
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())Greg_Deckler Thanks so much for taking the time to look into this and reply - this worked a charm!
