Forum Discussion
Create custom Table from different sources
Hi all,
I'm trying to show a table with some calculations of different sources and I would like to know what's the best approach to achieve that. I will try to sum up as much as possible.
The table I want to get is something like that:
USER_X
| Concept | Objective | Weight | Current total | % Achieved | Weight |
| Billing Area 10 | 401638.2 | 0.2 | 551022 | 1.37 | 0.2 |
| Billing Area 20 | 263342.16 | 0.15 | 187677 | 0.71 | 0 |
| Billing Area 30 | 324437.64 | 0.15 | 370785 | 1.14 | 0.15 |
| Total Area 10 | 15 | 0.2 | 12 | 0.80 | 0 |
| Total Area 20 | 12 | 0.15 | 13 | 1.08 | 0.15 |
| Total Area 30 | 10 | 0.15 | 11 | 1.10 | 0.15 |
| Total weight | 0.65 |
My current model is:
Objectives_table:
Person_id : key
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
Bills:
Person_id
Date
Area
Amount
Current total 10 = Sum of all bills amount from that person_id with area = 10
Total 10 = Count all bills from that person_id with area = 10
I will add some examples if the explanations isn't clear enough.
Thanks in advance!
mdelasheras，
Please check the following PBIX file.
https://1drv.ms/u/s!AhsotbnGu1NokzIAFEf6n2vq99BP
Regards,
Lydia
5 Replies
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,
LydiaThanks 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,
Lydia
