Forum Discussion
DAX Calculate with filter
Hello!
I suspect there is a very simple solution - so embarrassed to ask. But here goes...
I have a data table showing accounts receivable invoices. For every vendor with EntryType = "R", - I want to display the "Amount" column, and then offseting pmts. The challenge is for each InvoiceID, when a pmt is received for that invoice - that InvoiceID is displayed in the description. For example, (in blue) an invoice was created for Vendor A (InvoiceID = 2888), and then on 1/19/19, a ROA entry took place, with the description containing 2888.
The example in red, shows Vendor B with an Invoice ID 67408 - and then two partial pmts made against this same invoice.
| Date | Account | Invoice ID | EntryType | Vendor | Amount | Description |
| 12/26/2018 | 1055 | 2888 | R | VendorA | 5400 | |
| 1/19/2019 | 1055 | 11 | D | VendorA | -5400 | 2888 |
| 10/17/2017 | 1055 | 206 | R | VendorB | 6197.31 | |
| 10/23/2017 | 1055 | 40 | D | VendorB | -6197.31 | 206 |
| 3/27/2017 | 1055 | 67408 | R | VendorB | 8840.93 | |
| 3/27/2017 | 1055 | 11 | D | VendorB | -6000 | 67408 |
| 6/1/2017 | 1055 | 32 | D | VendorB | -2000 | 67408 |
| 11/26/2019 | 1055 | 3681 | R | VendorB | 5168 |
For Vendor A, the 5400 is the correct amount with only a single invoice and ROA.
However, Vendor B - the current measure is pulling in all ROA's for Vendor B, but ignoring the InvoiceID->Description match. For invoice 67408 the AcctsRecPaid measure should be $8,000 ($6000+$8000)
Here is my the measure I currently have - how would I create a filter to only pull in Description that equals the InvoiceID.
AcctsRecPaid = calculate([Amount1],Data[EntryType]<>"R",ALLEXCEPT(Data,Data[Vendor]))
I have shared the pbix file if it is needed. (the data is embedded in the datatable)
https://drive.google.com/open?id=1noh56_WMKeCfl8h_g68xt9ZAvj3ghBZK
Thank you!
2 Replies
- nhoward
Resolver I
For me, I try to work within the KISS principle (Keep it super simple). So I'd use the relationship feature of PowerBI to my advantage. I'd split your data table into 2 tables, one for Invoices and one for Payments (reductions). Then you can join the two tables (InvoiceNo to Description).
- amitchandak
Super User
Allexcept creates a kind of subtotal. Here seem like you need to have more than one column
refer :https://community.powerbi.com/t5/Desktop/ALLEXCEPT-in-a-multitable-setup/td-p/403205
Also, provide us what output you need.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin