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 know the DAX formula to generate the output table.

 

 

 

Thanks in Advance.

Regards,

Jay

 

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

5 Replies

  • mattbrice's avatar
    mattbrice
    Solution Sage
    8 years ago

    What is "Key"?   I assume that is an invoice number?  And did you intend for the 'Key' & 'Customer' in the second line of the required output to be 'M020' & 'Abc' respectively?   Is there a relationship between the two tables?  Or is there a bridge table in between - like  a 'Key' header table?

     

     

     

    • 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