Forum Discussion
Create a simple income statement
hi everyone, i have a couple of data tables: One is a loan table and the other is a deposit table. Both have different columns but there are two columns in each table which are related: Princ...
v-xicai
Community Support
Community SupportHi ivank8383 ,
According to your description, I create sample data like below.
You may create a calculated table, and then create two measure like DAX below.
Calculated table:
NewTable =
UNION (
SELECTCOLUMNS (
Loan,
"TableName", "Loan",
"DEAL_ID ", Loan[DEAL-ID],
"Principal Amount", Loan[Principal Amount],
"Interest Rate", Loan[Interest Rate]
),
SELECTCOLUMNS (
Deposit,
"TableName", "Deposit",
"DEAL_ID", Deposit[DEAL-ID],
"Principal Amount", Deposit[Principal Amount],
"Interest Rate", Deposit[Interest Rate]
)
)
Measure:
Net Principal Amount =
VAR _Loan =
CALCULATE ( SUM ( NewTable[Principal Amount] ), NewTable[TableName] = "Loan" )
VAR _Deposit =
CALCULATE (
SUM ( NewTable[Principal Amount] ),
NewTable[TableName] = "Deposit"
)
RETURN
_Loan - _Deposit
Net Interest Rate =
VAR _Loan =
CALCULATE ( SUM ( NewTable[Interest Rate] ), NewTable[TableName] = "Loan" )
VAR _Deposit =
CALCULATE ( SUM ( NewTable[Interest Rate] ), NewTable[TableName] = "Deposit" )
RETURN
_Loan - _Deposit
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Amy! this looks really good.
I have a couple of questions:
- how do i make the Net Principal and Net Interest to be a row instead of columns?
- Fyi, the deal ID are unique in both Loan and Deposit table. I just want to compute the Net total position.
- How do I compute a weighted average interest rate column which is weighted against the Principal amount?