Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Not applicable

Making a table with sums from two other tables



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)

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

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

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!

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 :


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

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

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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors