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

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