Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Making a table with sums from two other tables

Hello,

 

I would like to create a table that shows the total from two other tables for a specific date.  Additionally, I would like the option to drill down to see the components of that total (e.g. is it CP, FI, or a coupon?).  

 

 

Here is an example of one table (Table 1)

EntityCP/FIIssuerAmountMaturity
ACPT $     19,000,0006/28/2022
BCPAMCR $     10,000,0007/1/2022
CFIWMT $     25,500,0007/1/2022
DCPT $       8,000,0007/7/2022

 

Here is an example of the other table (Table 2).  

EntityCP/FIIssuerCoupon PaymentCoupon DateCoupon?
AFIGS $                10,0006/28/2022Coupon
AFIF $                15,0007/1/2022Coupon
BFIGOOG $                12,0007/8/2022Coupon
BFIMETA $                  5,0007/9/2022Coupon

 

Here is the desired result not drilled down

DateAmount
6/28/2022 $       19,010,000
7/1/2022 $       10,015,000
7/8/2022 $       25,512,000
7/9/2022 $         8,005,000

 

here is the desired Result Drilled down

DateAmountEntityIssuerType
6/28/2022 $               10,000AGSCoupon
6/28/2022 $       19,000,000ATCP
7/1/2022 $               15,000AFCoupon
7/1/2022 $       10,000,000BAMCRCP
7/8/2022 $       25,500,000CWMTFI
7/8/2022 $               12,000BGOOGCoupon
7/9/2022 $                 5,000BMETACoupon
7/9/2022 $         8,000,000DTCP

 

Is this possible?  Thank you in advance for your help!

4 REPLIES 4
Anonymous
Not applicable

@amitchandak I can't use UNION because the tables have different numbers of columns with different data in them.

@Anonymous , You can use append in power query and delete duplicates


Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

 

or you can use selectcolumns or summarize to get the columns you need

 

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

Anonymous
Not applicable

Can you be more specific as to how that's done with selectcolumns or summarize?  I cannot use append as Table 2 is already a generated table

amitchandak
Super User
Super User

@Anonymous , You need to create common date , entity , issuer , CP/FI, type etc

 

example common entity 

entity = distinct(Union(distinct(Table1[entity]),distinct(Table2[entity]) ) )

 

Date tbale you can create like

Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])

)

 

Join with both tables and use these table for slicer and in visual as common group bys

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.