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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
jyouens
Frequent Visitor

Combining 2 data sets

I am trying to compare what values our system holds vs Excel files that people keep up to date. The idea is to compare and contrast so we can pinpoint errors

 

Both data sets can have multiple entries for the fields I'm interested in, 'Name', 'Month', 'Cost'. The only way to 'join' them up is by the Name as they are the same in both tables.

 

Essentially, what I'm looking for is to sum the total for the client per month, from both data sets and add them to visuals. 

 

An example below

 

jyouens_0-1709129312462.png

 

 

1 ACCEPTED SOLUTION
v-zhouwen-msft
Community Support
Community Support

Hi @jyouens ,

The table data is shown below:

vzhouwenmsft_0-1709190834649.png

vzhouwenmsft_1-1709190841520.png

Please follow these steps:
1. Use the following DAX expression to create a table

 

Table = ADDCOLUMNS(
    SUMMARIZE('SYSTEM', 'SYSTEM'[Client Code], 'SYSTEM'[Client Name], 'SYSTEM'[Date]),
    "System Amount", CALCULATE(SUM('SYSTEM'[System Amount])),
    "Excel Amount",CALCULATE(SUM('EXCEL'[Excel Amount]),
     FILTER(ALL('EXCEL'),'EXCEL'[Client Code] = EARLIER('SYSTEM'[Client Code]) && 'EXCEL'[Client Name] = EARLIER('SYSTEM'[Client Name]) &&'EXCEL'[Date] = EARLIER('SYSTEM'[Date]))))

 

2. Final output

vzhouwenmsft_2-1709190921519.png

vzhouwenmsft_3-1709190929926.png

 

 


Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
jyouens
Frequent Visitor

worked perfectly, thank you @v-zhouwen-msft 

v-zhouwen-msft
Community Support
Community Support

Hi @jyouens ,

The table data is shown below:

vzhouwenmsft_0-1709190834649.png

vzhouwenmsft_1-1709190841520.png

Please follow these steps:
1. Use the following DAX expression to create a table

 

Table = ADDCOLUMNS(
    SUMMARIZE('SYSTEM', 'SYSTEM'[Client Code], 'SYSTEM'[Client Name], 'SYSTEM'[Date]),
    "System Amount", CALCULATE(SUM('SYSTEM'[System Amount])),
    "Excel Amount",CALCULATE(SUM('EXCEL'[Excel Amount]),
     FILTER(ALL('EXCEL'),'EXCEL'[Client Code] = EARLIER('SYSTEM'[Client Code]) && 'EXCEL'[Client Name] = EARLIER('SYSTEM'[Client Name]) &&'EXCEL'[Date] = EARLIER('SYSTEM'[Date]))))

 

2. Final output

vzhouwenmsft_2-1709190921519.png

vzhouwenmsft_3-1709190929926.png

 

 


Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.