The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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 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!
User | Count |
---|---|
78 | |
74 | |
42 | |
32 | |
28 |
User | Count |
---|---|
104 | |
93 | |
52 | |
50 | |
46 |