Hello everyone. This is my first post so I hope it goes well. I’ve included two screenshots of some mock tables I created in Excel to provide context. I have one master table called Financials which has all of the financial transactions associated with a claim number. I have another table called Test which is built from the Financials table.
The Test table contains the distinct values of claim numbers since in the Financials table a claim number can have multiple transactions. In the event that a claim number has multiple transactions, it would be on separate rows, as shown in the screenshot. Below is the formula I used to create my Test table with distinct claim numbers.
Test Table = distinct(Financials[Claim Number])
In reference to the screenshot for the attached Test table, I would like to create two columns titled Investigation and Settlement. In plain English, I would like the DAX formula to retrieve the check amount or even better, sum the check amount, if the payment type in the Financials table is Investigation. The same logic would apply when payment type is Settlement.
I’m thinking something like:
if lookupvalue from payment type in Financials = 'investigation' then sum check amount. The Test table will then have one row for each claim and it will have summed up all of the investigation payments and all of the settlement payments.
I hope I didn’t make this too confusing and thank you for helping. I’m sure it’s simple yet I can’t figure it out!