cancel
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.

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
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