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: Principal amount and Interest rate.
I would like to create a brand new table showing the net difference between the principal amounts and the annual interest amount for each product. the new table should be something like this:
| Product | Principal Amount | Annual Interest |
| Loan | 1,000 | 200 |
| Deposit | (2,000) | (50) |
| Net Position | (1,000) | 150 |
how may i do so? thanks
5 Replies
- amitchandak
Super User
ivank8383 , Try a new table like.
summarize(table, table[Product], "Principal Amount", sum(Table[Principal Amount]) ,"Annual Interest" , sum(Table[Annual Interest]))
You can have this with visual with measures sum(Table[Principal Amount]) and sum(Table[Annual Interest])
https://community.powerbi.com/t5/Desktop/Traditional-Financial-Statements/td-p/7223
Thanks Amit!
As i am very new to this, may i trouble you further to elaborate your suggestion?
As i have 2 data tables (one for loan and one for depo) which have different columns, do I create 2 new tables with summarize funciton?
To give you all more colors, my data table are like this:
Table Name: Loan
DEAL-ID Principal Amount Interest Rate Type Table Name: Deposit
DEAL-ID Principal Amount Interest Rate Tenor if so, how do i combine them into a single visual later?
thanks!
I searched and tried the following to at least create a row based on the Loan table:
IncomeStatement2 = var combinetable = ADDCOLUMNS('OS LOAN',"Principal Amount",SUM('OS LOAN'[P-SGD]),"Average Interest",'OS LOAN'[AVEINT]) return SUMMARIZE(combinetable,[Principal Amount],[Average Interest])the result looks weird. I got 67 rows producing same number (which presents the sum of all P-SGD) under "Principal AMount" column while there are various numbers for "Average Interest" column. Fyi, AVEINT is a measure with following formula "AVEINT = SUMX('OS LOAN',[RATE]/100*[P-SGD]) / SUM([P-SGD])".
