Create custom Table from different sources

[mdelasheras]

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!

[Anonymous]

@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

[mdelasheras]

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!

[Anonymous · Accepted solution]

@mdelasheras，

Please check the following PBIX file.
https://1drv.ms/u/s!AhsotbnGu1NokzIAFEf6n2vq99BP

Regards,
Lydia

[pbiforum123]

File is currently not available in one drive. Can you please share the same.

[mdelasheras]

Very clear, thank you so much Lydia!