Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Hi @Jayadev,
Great to help! Could you accept my reply above as solution to close this thread?
Regards
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?
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
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
Thanks a lot, it works fine.
Hi @Jayadev,
Great to help! Could you accept my reply above as solution to close this thread?
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |