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

    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-IDPrincipal AmountInterest RateType
        
        

     

    Table Name: Deposit

     

    DEAL-IDPrincipal AmountInterest RateTenor
        
        

     

    if so, how do i combine them into a single visual later?

     

    thanks!

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