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
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
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.
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.
Proud to be a Super User!
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.
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
do you create relationships between tables?
Proud to be a Super User!
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 |
Proud to be a Super User!
Yes, I will do that next time.
Thanks!
you are welcome
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
108 | |
107 | |
92 | |
68 |
User | Count |
---|---|
167 | |
130 | |
129 | |
95 | |
91 |