Forum Discussion
Create custom Table from different sources
mdelasheras，
Please check the following PBIX file.
https://1drv.ms/u/s!AhsotbnGu1NokzIAFEf6n2vq99BP
Regards,
Lydia
mdelasheras,
An idea is to unpivot columns in your Objectives_table, then merge the Objectives_table and Bills table.
In order for use to provide you detailed steps, please share sample data of your Objectives_table and Bills table, then post expected result based on the sample data.
Regards,
Lydia
Thanks Anonymous! I will add a detailed sample.
Objectives_table:
| Person_id | Year | Billing_10_obj | Billing_20_obj | Billing_30_obj | Billing_10_weight | Billing_20_weight | Billing_30_weight | Total_10_obj | Total_20_obj | Total_30_obj | Total_10_weight | Total_20_weight | Total_30_weight |
| 1 | 2018 | 333300 | 202000 | 121210 | 0.5 | 0.1 | 0.1 | 8 | 6 | 5 | 0.1 | 0.1 | 0.5 |
| 2 | 2018 | 144000 | 80000 | 50000 | 0.2 | 0.15 | 0.15 | 1 | 2 | 3 | 0.2 | 0.15 | 0.15 |
| 3 | 2018 | 220000 | 222000 | 222200 | 0.6 | 0.4 | 0.3 | 3 | 4 | 2 | 0.4 | 0.05 | 0.05 |
Bills_table:
| Person_id | Date | Area | Amount |
| 1 | 09/01/2018 | 30 | 684641 |
| 1 | 18/04/2018 | 30 | 46465 |
| 2 | 14/03/2017 | 10 | 44100 |
| 2 | 27/02/2018 | 10 | 89620 |
| 2 | 18/04/2018 | 20 | 47512 |
| 2 | 18/04/2018 | 30 | 15687 |
| 2 | 29/03/2018 | 30 | 51510 |
| 3 | 22/01/2018 | 10 | 14541 |
| 3 | 16/04/2018 | 20 | 54981 |
Expected result for person_id = 2 and year = 2018:
| Concept | Objective | Weight | Current total | % Achieved (current/obj) | Weight reached* |
| Billing Area 10 | 144000 | 0.2 | 89620 | 0.62 | 0 |
| Billing Area 20 | 80000 | 0.15 | 47512 | 0.59 | 0 |
| Billing Area 30 | 50000 | 0.15 | 67197 | 1.34 | 0.15 |
| Total Bills Area 10 | 1 | 0.2 | 1 | 1.00 | 0.2 |
| Total Bills Area 20 | 2 | 0.15 | 1 | 0.50 | 0 |
| Total Bills Area 30 | 3 | 0.15 | 2 | 0.67 | 0 |
| Total weight | 0.35 |
* Weight reached is 0 if % < 1, otherwise is weight
Many thanks!
mdelasheras，
Please check the following PBIX file.
https://1drv.ms/u/s!AhsotbnGu1NokzIAFEf6n2vq99BP
Regards,
LydiaVery clear, thank you so much Lydia!
File is currently not available in one drive. Can you please share the same.
