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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Combining Totals from 2 different tables

Hi everyone,

 

I need a help regarding combining the results of 2 different source tables in Power BI. Please see below:
rbsantiago_0-1676212229606.png

 

The first table is static, with manual input, where the dates defined always starts at first of the month(eg. 1/1/23, 2/1/23, etc) while in the second table, dates varies.
My end goal is to calculate the percentage achieve from the target to the actual live data.

I tried to merge the table data but their respective result changed.

I also tried to have a one way relationship between the common field of the table which is the department but still, does not displayed the correct values.

Also, either of the 2 values can have an additional department, which ever is updated first, it may be first table or the second table.

 

So my final approach is to have the 2 tables having separate visuals but have a third table with their combined result. But I cant figure out how to achieve this.

 

Can anyone help me with this? A step by step guide would be much appreciated.

Thank you in advance.

 

 

Best regards,

Ray

11 REPLIES 11
Migasuke
Super User
Super User

Hi @Anonymous ,

You refer to Dates but in your tables you have no dates column. Are those two example tables completely same as your data source?

Thanks.



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Anonymous
Not applicable

Hi,

 

Thank you for your response.

The table have a date column. It is the Qtr 1, Qtr2, etc. You can expand/drill those columns to display by months. As I mentioned, the dates are in this format: (eg. 1/1/23, 2/1/23, etc)

Qtr 1  Qtr 2  
January  February  March  April  May  June

 

They are not in same data source. The Target table is a user defined table, while the Actual table comes from another live source though they reside on same database.

 

 

Thanks.

pls provide some sample data of two tables.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi,

Sorry but it seems that I cant attach an excel or zip file on here so, I can only give a screenshot of the sample data. Please see below images.
rbsantiago_0-1676255741512.png

rbsantiago_1-1676255847239.png

 

@Anonymous 

pls see the attachment below

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi @ryan_mayu ,

 

 

I tried to do your sample, however I have a zero result.

rbsantiago_0-1676267923068.png

Actually, I am newbie in PowerBI and started using the application 3 days ago.

All other reports were already done exept of the final result which is computation of Actual/Target.

So here are the steps I did:
1. Create a department table 

Department = DISTINCT('Target Name'[Department])
 
2. Create a Date Table
Selection Date = CALENDAR(MIN('Target'[R72 Date]),MAX('Target'[R72 Date]))
 
3. On the actual value table, I created the calculation column:
Performance = sum('Actual Data'[Total Processed)/SUM('Target'[TargetValue])
 
4. From the relationship connection, I linked "R72 Date" and "Actual Date" to step 2 date, and 'Target'[Department] and 'Actual Data'[Department] to step 1 department.

5. Finally From a matrix table:
Rows = (Created Department)
Column = (Created Date)
Values = (Performance Calculation)
 
Is there any steps I missed?
 

do you create relationships between tables?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi @ryan_mayu ,

 

Yes I did. (Step 4)
I am thinking, I think I am able to create the calculation I need.

Though it is a different approach, your sample gave me an idea to do it.

Here is my thought:
1. Clean the 2 soruce table, remove un needed columns from actual and target

2. Make the names of common columns identical for both table

3. Append the 2 tables. This will just add another column which is not present on the other table.

4. Create a computation matrix on the fields dividing the 2 concerned columns

5. Arrange the columns in the matrix as usual.

 

Feel free to comment if you have a better suggestion or the procedure I did have a consequence.

Thank you so much for your help and time!
Your sample help me so much!

you can paste your raw data as table here. that will be better than screenshot

something like below

a b c
1 3 5
2 4 6




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Yes, I will do that next time.

Thanks!

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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