Forum Discussion

Jayadev's avatar
Jayadev
Helper I
8 years ago
Solved

Summarising values from two tables

  Hi,   I have two tables where in Bills are stored in one table & payments are in another table, need to create a new table with following information.    Both tables are in data model, I need to...
Tutorial Requests
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Jayadev,

     

    Based on my test, the formula(DAX) below should work in your scenario. :smileyhappy:

    Table = 
SUMMARIZE (
    Table1,
    Table1[Bill Date],
    Table1[Key],
    "Amount", SUM ( Table1[Amount] ),
    "Received", SUM ( Table2[Received] ),
    "Remaining", SUM ( Table1[Amount] ) - SUM ( Table2[Received] )
)

     

    Regards

  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Jayadev,

     

    Great to help! Could you accept my reply above as solution to close this thread? :smileyhappy:

     

    Regards

Jayadev's avatar
Jayadev
Helper I
8 years ago

Thanks for the attention.

You are right, Key is, Invoice Number, there is relationship, table A will have multiple rows, Table B one record for each invoice number.  

 

Yes required output to be M010, M020 respectively.  I made mistake in my example, you pointed out rightly

Thanks once again for attention, please help me to apply the DAX.

 

Regards,

Jay

 

 

 

 

 

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi Jayadev,

 

Based on my test, the formula(DAX) below should work in your scenario. :smileyhappy:

Table = 
SUMMARIZE (
    Table1,
    Table1[Bill Date],
    Table1[Key],
    "Amount", SUM ( Table1[Amount] ),
    "Received", SUM ( Table2[Received] ),
    "Remaining", SUM ( Table1[Amount] ) - SUM ( Table2[Received] )
)

 

Regards

  • Jayadev's avatar
    Jayadev
    Helper I
    8 years ago

    Thanks a lot, it works fine.

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      8 years ago

      Hi Jayadev,

       

      Great to help! Could you accept my reply above as solution to close this thread? :smileyhappy:

       

      Regards