Summarising values from two tables

[Jayadev]

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

[v-ljerr-msft · Accepted solution]

Hi @Jayadev,

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

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

Regards

[mattbrice]

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]

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 · Accepted solution]

Hi @Jayadev,

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

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]

Thanks a lot, it works fine.

[v-ljerr-msft · Accepted solution]

Hi @Jayadev,

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

Regards