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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors