Combining Totals from 2 different tables

[rbsantiago]

Hi everyone,

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

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

[Migasuke]

Hi @rbsantiago ,

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.

[rbsantiago]

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.

[ryan_mayu]

pls provide some sample data of two tables.

[rbsantiago]

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.

[ryan_mayu]

@rbsantiago 

pls see the attachment below

[rbsantiago]

Hi @ryan_mayu ,

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

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?

[ryan_mayu]

do you create relationships between tables?

[rbsantiago]

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!

[ryan_mayu]

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

[rbsantiago]

Yes, I will do that next time.

Thanks!

[ryan_mayu]

you are welcome