Forum Discussion

ivank8383's avatar
ivank8383
Frequent Visitor
6 years ago

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...
ivank8383's avatar
ivank8383
Frequent Visitor
6 years ago

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])". 
v-xicai's avatar
v-xicai
Community Support
6 years ago

Hi 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.

  • ivank8383's avatar
    ivank8383
    Frequent Visitor
    6 years ago

    Thanks Amy! this looks really good.

     

    I have a couple of questions:

    1. how do i make the Net Principal and Net Interest to be a row instead of columns?
    2. Fyi, the deal ID are unique in both Loan and Deposit table. I just want to compute the Net total position. 
    3. How do I compute a weighted average interest rate column which is weighted against the Principal amount?